primary key...

[Westley]

I have four tables:

tblEmployees (employeenumber, firstname, middlename, lastname, address, city, state, homephone, cellphone, hiredate, salary, terminationdate, referredby, Notes, DateCreated, UserCreated, Date Modified, User Modified)

tblVacationDays (VacationDay1, VacationNote1, VacationDay2, VacationNote2, VacationDay3, VacationNote3, VacationDay4, VacationNote4, VacationDay5, VacationNote5, VacationDay6, VacationNote6, vacationDay7, VacationNote7, VacationDay8, VacationNote8, VacationDay9, VacationNote9, VacationDay10, VacationNote10)

tblSchedule (MondayAM, MondayPM, TuesdayAM, TuesdayPM, WednesdayAM, WednesdayPM, ThursdayAM, ThursdayPM, FridayAM, FridayPM)

tblLeads (DateLeadsAcquired, LeadsAcquiredAM, NumberCallsAM, LeadsAcquiredPM, NumberCallsPM, LeadNotes)

All this information is revelvant to each employee. What should I make the primary key, in each table) to connect the 4 tables?

Thanks!
Wes

 

[Damon Heron]

Whew! Where to Start? Your tblEmployees is okay, I guess. Then we have
tblVacationDays....
How about:
VacationID
VacationDay
VacNotes
Employeenumber (as a foreign key from Employee table)

It is not a good idea to repeat data like day2,3,4,etc, and note2,3,4 -

Then add another Employeenumber field to tblSchedule and tblLeads which is
also a foreign key pointing to that employee...

One employee can have multiple vacation days, multiple schedule days, and
multiple leads, right? think about your relationships and what data you want
to capture....

HTH
Damon

I have four tables:

tblEmployees (employeenumber, firstname, middlename, lastname, address,

city, state, homephone, cellphone, hiredate, salary, terminationdate,
referredby, Notes, DateCreated, UserCreated, Date Modified, User Modified)

tblVacationDays (VacationDay1, VacationNote1, VacationDay2, VacationNote2,

VacationDay3, VacationNote3, VacationDay4, VacationNote4, VacationDay5,
VacationNote5, VacationDay6, VacationNote6, vacationDay7, VacationNote7,
VacationDay8, VacationNote8, VacationDay9, VacationNote9, VacationDay10,
VacationNote10)

tblSchedule (MondayAM, MondayPM, TuesdayAM, TuesdayPM, WednesdayAM,

WednesdayPM, ThursdayAM, ThursdayPM, FridayAM, FridayPM)

tblLeads (DateLeadsAcquired, LeadsAcquiredAM, NumberCallsAM,

LeadsAcquiredPM, NumberCallsPM, LeadNotes)

All this information is revelvant to each employee. What should I make the

primary key, in each table) to connect the 4 tables?

 

[Westley]

Yes, each employee can have multiple vacation days, multiple schedule days, and multiple leads. I guess Im not sure how would I just have one field for each of these.

Employees can have up to 10 days to use for their vacationdays. I want to be able to track what day they use them and the reason for them using that particular day.

I need to track the amount of calls & leads during an AM shift and a PM shift. I need seperate values for both. Some employees work both.

Im pretty confused and don't know how I would do this properly. I'm just thinking of how I would make a form and have a field for each shift they work (yes/no check boxes) and 10 blank entries. with date and a note for each.

Please help! Thanks again.
Wes

 

[Damon Heron]

Suggest you stop everything, take a deep breath, and read a little more
about relational db design. Here is a place to start:
http://office.microsoft.com/assistance/preview.aspx?AssetID=HP051891361033&CTT=98

Damon

Yes, each employee can have multiple vacation days, multiple schedule

days, and multiple leads. I guess Im not sure how would I just have one
field for each of these.

Employees can have up to 10 days to use for their vacationdays. I want to

be able to track what day they use them and the reason for them using that
particular day.

I need to track the amount of calls & leads during an AM shift and a PM

shift. I need seperate values for both. Some employees work both.

Im pretty confused and don't know how I would do this properly. I'm just

thinking of how I would make a form and have a field for each shift they
work (yes/no check boxes) and 10 blank entries. with date and a note for
each.

 

[Westley]

Alright, I read the article and I understand it. But I still don't understand something - if there is only one note field and one vacation day field - where does it save all 10 days for a employee? Would it just put it all in those fields? Same goes for schedule (mon-fri Am & PM).

Thats the part I'm really confused about.

Thanks again!
Wes

 

[Damon Heron]

Each record would hold the info for each vacation day, so you would have the
date recorded for each day that an employee took, reference by the employee
number. So to look at this data (in a form or report) you would query the
table by employee number and retrieve a list of vacation days for that
employee....
A table contains MULTIPLE records! So, as an example the Vacation table
might look like this:

ID VDate Notes Employeenum
1 6/12/04 whatever 3
2 4/11/04 dddd 5
3 7/03/04 ddddssssdd 3

In this example, Employee 3 took two vacation days, emp 5 took one. As the
records grow, you can retrieve all the info you need about vacations...

Damon

Alright, I read the article and I understand it. But I still don't

understand something - if there is only one note field and one vacation day
field - where does it save all 10 days for a employee? Would it just put it
all in those fields? Same goes for schedule (mon-fri Am & PM).

 

[Chris Nebinger]

I'm just going to throw something out there, although
Damon is very correct...

With your original setup, how would you handle finding out
how many vacation days used?
Day1+Day2+Day3+etc... Have to catch nulls.

What about answering the question of number of leads...

MonAM + MonPM + TueAM + etc...

Do you see how hard it would be?

What about the business rule that says employees can now
have 11 days? 14 Days? You would have to change the
table structure, thus changing all queries, forms, macros,
modules, reports based off that table.


Chris Nebinger

 

[rpw]

Hi Westley,

Maybe if you populate the table with some 'data' it might be easier to understand where the information for all ten days off is stored. In the following sample, I have four records, two of which are for employee #1 (so that's two of those ten days). You can see how many more can be added, yes?

tblVacationDays
VacationID VacationDay VacNotes Employeenumber
1 06/14/04 Birthday 1
2 06/15/04 Son'sBD 1
3 06/18/04 Fishing 2
4 06/14/04 Skiing 3

From the data you can then query and find out that employee #1 has used 2 vacation days, you can create a report that lists employee # 1's vacation days and all of the associated notes, you can list all employees that had 6/14/04 as a vacation day, etc.

How you store the data is often quite different than how you want to see the information. Keep that in mind when you are building your table structure.

Try this article on 'normalization' and see if it doesn't help you to understand a little better.

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")