Choosing a primary key field

[Access_Novice]

I have a table with timesheet data for many employees. Each row lists the employee and the hours to a specific date. For any given month, an employee might appear 20 times, once on each row due to 20 workdays in a month. Then the next month, they could appear 20 times again, etc. Each person has an Employee ID number (unique identifier).



Although the employee ID is unique to each person, can I use that as a primary key, even though each person (and therefore their employee ID) can appear many times?

If not, what would be the best primary key to use?

Eventually I will have two other tables. One contains billed hours by week ending date, and the other file contains hours paid by week ending date. I can assign employee IDs to people in those tables, but there will also be many instances of the same person appearing more than once. So for these two tables, I'm just not sure what to use for the primary key.

[Bob Fitz]

No you can not use the employee ID as a primary key because it will not be a unique identifier. IMHO the best choice would be a new field of the auto-number type.

[pbaldy]

You can't use employee ID alone as a key, because it repeats. You can use the ID and the date field together as a composite key, since presumably the combination can't repeat. You could also use an autonumber field as the primary key.

[pbaldy]

This is what happens when you get distracted by an email from the boss.

[June7]

Do you even need a primary key? If there is not a related table where the key will be needed as a foreign key, then don't worry about it. The real concern is that the employeeID and date pairs are not allowed to repeat. This can be assured by defining the two fields as a compound primary key or just as a compound index.

An autonumber field can be useful in some queries but doesn't have to be PK. The autonumber field can be generated later if the need arises.

[pbaldy]

I always have a primary key, though that may be because I use SQL Server a lot, and a linked SQL Server table without a primary key is read-only in Access. I figure that just in case an Access db might get upsized at some point, I always have keys so nothing breaks.

[Access_Novice]

Do you even need a primary key? If there is not a related table where the key will be needed as a foreign key, then don't worry about it. The real concern is that the employeeID and date pairs are not allowed to repeat. This can be assured by defining the two fields as a compound primary key or just as a compound index.

An autonumber field can be useful in some queries but doesn't have to be PK. The autonumber field can be generated later if the need arises.

I always thought you had to have a primary key. I want to be clear on what I am trying to do here so I will reiterate. I have a table with timesheet charges. Employee IDs will repeat. Can employee IDs and dates repeat? Possibly, so I don't think I can combine those two fields as a primary key.

I have a second table with billed data. This shows last names, dates and hours billed. A third table shows hours paid. This also shows last names and hours paid and timesheet dates paid.

Ultimately I want to connect all three of these tables and show that the timesheet hours for any person and date (in table 1) = the hours billed for that same person and same date = the hours paid for that person and date. The data supplied to all three tables will be imported from an Excel spreadsheet.

I am trying to reconcile all three tables together. So I can't use an employee ID as a primary key, or the last name since those will repeat. I can't combine last name & timesheet date because a few of those might repeat. Does this change anything? Do I still need a primary key in this case?

Or is this a case of a many to many relationship? If so, what is the best approach to handling that?

[June7]

The purpose of primary key is to associate records of related tables. The primary key would be saved as foreign in another table. A field (or several fields together) can be set to not allow duplicates. They do not have to also be defined as primary key.

Names make very poor primary keys. Each record in Employees table should have a unique ID as primary key. That ID can be an autonumber or assigned. This ID should be saved into related tables, not the employee last name.

Each employee will have many time periods and each time period will have many employees. That is many-to-many relationship.

Why would you allow more than one record for each employee/date? Are the hours in a day broken up by some other criteria?

[Access_Novice]

Why would you allow more than one record for each employee/date? Are the hours in a day broken up by some other criteria?

Yes. For example on any given date, an employee might have 5.0 hours charged to one task, 2 hours to another task and 1 hour to a third task. In this example, the employee name and the timesheet date would appear on 3 rows, and each row would have a unique task name.

Each record in Employees table should have a unique ID as primary key. That ID can be an autonumber or assigned.

Here is an idea I had. Concatenate employee Name & date & hours. This should give a unique value for each row. That is set up as a calculated field in my table. But I can't designate that as a Primary Key. Is this because it's a calculated field?

[June7]

Correct, calculated field cannot be primary key. The combination of EmpID, Date, task code (not hours) should be unique. This can be enforced by setting the three fields as a compound index. This table does not need primary key unless it has a related table that needs foreign key.

Do you have a table of Employees? This would have a unique record for each employee.

Do you have a table of tasks? This would have unique record for each task.

Then you should have a junction table that has records to associate employees with tasks and the hours for each task. This table would use EmpID and TaskID as foreign keys.

[Access_Novice]

Correct, calculated field cannot be primary key. The combination of EmpID, Date, task code (not hours) should be unique. This can be enforced by setting the three fields as a compound index. This table does not need primary key unless it has a related table that needs foreign key.

Do you have a table of Employees? This would have a unique record for each employee.

Do you have a table of tasks? This would have unique record for each task.

Then you should have a junction table that has records to associate employees with tasks and the hours for each task. This table would use EmpID and TaskID as foreign keys.

Ok. I'll think about that. But for now, I tried something. I believe it was what you were talking about. For now I have two tables, one called Timesheet and Paid. In the timesheet table, I did an autonumber primary key. Then I clicked the Index button and created an index called 'unique' which was defined by name, date and task code.

Then I went into the 'Paid' table and did the same thing. But when I query the two tables, I am having a mismatch. One guy is id 29, and in the other table, ID 29 is a different person. What did I do wrong? Was I supposed to do this on only one table?

[June7]

Cannot link on autonumber fields - they are generated by each table independently of any other table and therefore they will be different in each table and have no relationship. Appears you still don't understand how primary and foreign keys work.

Does Paid table have the name, date and task fields? If so, then possibly a 1-to-1 relationship and could combine the two tables into one which would greatly simplify this. Otherwise, join the two tables on the 3 fields - a composite link - join type "Include all records from Billed and only those from Paid that match".

[Access_Novice]

Does Paid table have the name, date and task fields? If so, then possibly a 1-to-1 relationship and could combine the two tables into one which would greatly simplify this.

Yes it does and that is exactly what I had in mind just now. I'm not worried about the 'Billed' table now. So the 'timesheets' table and the 'paid' table does have name, date and task code fields. So I would have to link those right? Do I need to designate "name & date & task code" as a primary key in one table as well as the other? If so, I tried this but maybe I got confused with the AutoNumber part of it. How would I go about doing this?

Also, how do you know when you will have one to one, one to many and many to many relationships? Is there a general rule that you can use to easily recognize these situations?

[June7]

Can designate the 3 fields as compound primary key in both tables but I doubt it will matter if you don't. At least set them as compound index.

What happened when you tried to link them in query?


Ask this question: Will entity A associate with many entity B and entity B can associate with many entity A?

If yes and no, this is a 1-to-many relationship. Example: an individual can have many cell phone numbers but each cell phone associates with only one person.

If yes and yes, this is a many-to-many relationship. Example: a purchase order can have many products and each product can be used with many purchase orders.

[Access_Novice]

Can designate the 3 fields as compound primary key in both tables but I doubt it will matter if you don't. At least set them as compound index.

For both tables I created a compound index based on lastname & weekendingDate & taskCode. How do I link them in table relationships?