I have a UsageData table with the following fields: Facility, UsageDate, ProductID, BegInventory, PurchaseAmount, EndInventory. The user can only insert one entry per Facility per month (UsageDate gets set to mm/1/yyyy programatically) per ProductID - therefore the combination of these three fields is what makes that record unique, and together they serve as the primary key. I have seen and heard that using multiple fields as a primary key is not a good idea. However, I have thought about the AutoNumber option, but unless I am missing something very obvious, I do not see how this will help me since the user would still be able to insert an entry for the same Facility, UsageDate, and ProductID as long as the BegInventory, PurchaseAmount, and EndInventory are different than the "original" entry. Is there something obvious that I am missing, or is my only option in this case to use multiple fields as my primary key?
I have asked this question before and what you are asking about is called a composite key or compound key. The thread I started turned into quite a debate over using a composite key or adding and autonumber field, a surrogate key. You will find people on both sides of the debate. With people on both sides, I think either way is acceptable.
My personal opinion is if you can uniquely identify a record using the fields in the table then why add an additional field to your table. You may find that creating your application around a composite key is a tiny bit more challenging. You will have to make sure all of the fields are entered in the table otherwise you will get a very unfriendly error message from Access about your Primary Key.