Re: ACCESS 2007: need help in database breakdown and table creatio

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Comments in line.


"Lord Kelvan" <the_iddiot@xxxxxxxxxxx> wrote in message
news:c8d3204c-2a8f-4eca-aec9-45176e9e753d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
the job site table is designed to store what employees are in what
job
site so you can do a quick seach so see this information

the pay table allows you too see over time

ie

tblpay
payid employeeid PreviousRate CurrentRate DateofRaise
1 1 50000 55000
1/1/2008
2 1 55000 58000
1/5/2008

does this make sence

No. The below is better and is Relational. PreviousRate and
CurrentRate cause someone to enter redundant data.

tblPay
PayID EmployeeID Rate EffectiveDate
1 1 55000 1/1/2008
2 1 58000 1/5/2008

Queries to extract required information are easy to create. The
latest date is always the last rate. You can create a Report based on
a query that will list every rate and effective date in date order
for each employee.


o right i seem to have forgotten a table you need a work table which
will have the employeeid and the jobsiteid so you can see the list
of
employees on a site.

tblemployee
Employeeid LastName FirstName DateHired ApprenticeLevel
ApprenticeSchoolStartDate ApprenticeSchoolEndDate Comments
1 bob jones 1/1/2007
1 1/5/2007
6/9/2007 he is fat
2 frank smith
1/8/2007

I would only include the apprentice School info in the tblEmployee
record if every single employee must have that school and if there
will never be any other training noted in your application. If there
will be other training for some or all employees then I'd have a
tblTraining. tblTraining would be in a many-to-many relationship with
tblEmployee. tblTraining would list every course of training that an
employee might take. The junction table, tblEmployeeTraining would
list every instance of an employee and a course of training.


tbljobsite
jobsiteid Jobsitename
1 that big job
2 that other job

tblwork
employeeid jobsiteid projectleader
1 1
2 1 yes
2 1 yes


I have trouble with the ProjectLeader issue. Is it a permanent Rank
that once attained stays with that person? If so and thee are other
Ranks to consider; President, Vice President, other officers, Clerk of
the Works, etc. then there should be a tblRank that lists every
possible rank. It would be a lookup table Is it simply situational:
this person is the Project leader on this particular job but on other
jobs s/he is not???


i hope that helps

regards
Kelvan

HTH
--
-Larry-
--


.

Relevant Pages

  • Re: Join has reinvented itself
    ... As I said, I created the original join before there were any records, and the interface set it up with 2 extra instances of tblInspection. ... I assumed that was correct without giving it a lot of thought, so when it behaved differently I was puzzled, but as I said it came together in my head when you pointed out that there are three separate employees and therefore there must be three separate instances of tblEmployee. ... The InspectedBy, RepairedBy, ApprovedBy fields in a single Inspection record refer to 3 different employees, so 3 separate tblEmployee instances are required. ... In the Relationships window I dragged EmployeeID on top of InspectedBy, clicked Create, and all was well. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Selecting a Particular Record from Groups of Records in a Table
    ... tblEmployee and tblAppraisalHistory. ... and also contain appraisal date (AppraisalDate), score, ... So, for a given employeeID in tblAppraisalHistory, I want to get the ...
    (microsoft.public.access.queries)
  • Selecting a Particular Record from Groups of Records in a Table
    ... tblEmployee and tblAppraisalHistory. ... and also contain appraisal date (AppraisalDate), score, ... So, for a given employeeID in tblAppraisalHistory, I want to get the ...
    (microsoft.public.access.queries)
  • RE: yes/no
    ... For one thing you will probably want a Meals table. ... Employees table (tblEmployee), and the foreign key of your trips table ... the Relationships window by adding tblEmployee and tblTrips to the ... relationship window and dragging EmployeeID from one table to the other. ...
    (microsoft.public.access.forms)
  • Re: Table with 230 Fields....whats a better alternative?
    ... > tblEmployee (EmployeeID, Firstname, LastName) ... > there's 227 training records one employee could have a score against. ... 200-300 entries means that if someone was looking for 'Forklift' but it had ...
    (comp.databases.ms-access)