trouble selecting PrimaryKeys

[JukeboxHero]

The idea is to write a db to keep track of people being absent due to various illnesses. But every person can become sick more than once of course.
I have a personnel tabel:

[Personnel]
*PersonnelID
various info

Now in theory - if I got the Boyd Codd thingie right - I could make a tabel [Absent] like this:

[Absent]
*PersonnelID
*AbsentID
various info

but, then you'd get something like

PersonID = 1
AbsentID = 1
various info

PersonID = 1
AbsentID = 2
various info

PersonID = 2
AbsentID = 1
various info

as you can see you'd have to manually change AbsentID, since you can't have it done automatically. Which is a major pain in the ass to update.

or I could do:

[Absent]
*AbsentID
PersonID
various info

using only one Primary Key (AbsentID)rather than using both of them as PKs. This way I could also use have my nummering done automatically.

Now for the actual question: is there a way to do it like the first one - so with 2 PKs - without making updating extremely annoying?

 

[Darrylles]

Hi,

Name the ID field in the ONE table: PK.
Name the foreign key in the many table: FK.

The PK is always defined as the tables primary key, the FK is not.

If you define the relationship from PK to FK, you don't have to manually change the FK value, it is populated automatically by Access.

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[JukeboxHero]

Could you please explain that again? perhaps using an example?

I'm not big on Access and databases in general and I'm not sure what you're referring to with 'the ONE table' and 'the many table'

Basically I'm rather new to all this.

 

[cjashwell]

You need a one-to-many relationship between your Personnel table and your Absences table. The Foreign Key (FK) in the Personnel table relates that table to your Absences table (where it is a Primary Key - PK). So the Personnel table is a 'one' table - it should contain only one row per employee - and the Absences table is a 'many' table - each employee can be absent many times (this assumes each absence has only one reason, of course ...)

Now, if you set your relationship between the two tables to 'Enforce Referential Integrity' Access will update the links between them. When you add a new Absence in the absence table, you enter the employee ID and Access will generate a new Absence ID.

To set the referential integrity rule, open the relationships window, right click on the relationship in question, and tick the 'Enforce Referential Integrity' box, as well as the Cascade Update/Delete Related fields boxes (this way, should you ever delete an employee row the related absence rows will be deleted also).

Hope this helps.

 

[JukeboxHero]

thnx guys \o/ I think I see what you mean

 

[lespaul]

So for your description:

tblEmployee
EmployeeID (PK) Name
1 Bob
2 Joe
3 Mary

tblAbsentCodes
AbsentCodeID (PK) Reason
1 Emp. Sick
2 Emp. Child Sick
3 Death in family

tblEmployeesAbsent
AbsentID (PK) EmpID (FK) AbsentCode (FK)
1 1 1
2 2 3
3 1 2

So, all the PK's are autonumbers. The FK's are long integers. The above tblEmployeesAbsent has records indicating bob was sick, joe had a death in the family, and then joe's kid was sick.

There is a one to many relationship between tblEmployee.EmployeeID and tblEmployeeAbsent.EmployeeID (for each one employee in tblEmployee there can be many records in tblEmployeeAbsent); there is a one to many relationship between tblAbsentCodes.AbsentID and tblEmployeeAbsent.AbsentID (for each AbsentCode in tblAbsentCode there are many records in tblEmployeeAbsent).

HTH

Leslie