 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / March 2006Using Autonumber as PK?
Does Access have a problem using an autonumber for a PK? I've got 3 tables. tbl-Parts pk-PartsID - Autonumber PartNumber - Number PartDescription - Text tbl-Universal pk-UniversalID - Autonumber UniversalPartNumber - Number UniversalDescription - Text tbl-UniversalParts fk-PartsID - Number fk-UniversalID - Number I then set up some forms per the instructions on this topic I received last week, basically one form is Parts with a subform Universal, and another form Universal with a subform Parts. My problem is finding a work around because both PartNumbers and UniversalPartNumbers may have duplicates at times and can't be used as the pk. Access doesn't seem to like my setup and my friend told me it was the autonumber as the pk but he couldn't explain why or what I needed to do to work around this. Thank you. Autonumbers are generally used as PK. Did you set a one-to-many relation between the tables? Click on TOOLS - Relationships and add the tables by clicking on the Show Table icon that is a yellow plus sign with a datasheet. The primary key will be in bold. click on the primary key and drag to the foreign key of the related table. Select Enforce Referential Integerity and Cascade Updates. I see you are using a number field for part numbers. I think you need to use text for this datatype as you will never need to do math (add, subtract, multiply, or divide) these numbers. You can have a problem if there is a dash in the number. > Does Access have a problem using an autonumber for a PK? I've got 3 tables. > tbl-Parts [quoted text clipped - 20 lines] > > Thank you. No problem, in fact it's quite normal to do that. -Dorian > Does Access have a problem using an autonumber for a PK? I've got 3 tables. > tbl-Parts [quoted text clipped - 20 lines] > > Thank you. Thanks for the input/advice. I'll change the PartNumber & UniversalPartNumber to text, that may have been my whole problem because my numbers do have dashes in them. And yes I have one-to-many relationships between, (tbl-Parts - tbl-UniversalParts) and (tbl-Universal - tbl-UniversalParts) and I have selected Enforce Referential Integerity and Cascade Updates in each relationship. Thanks again to all of you. You probably don't need to enable cascading updates since you're going to be using autonumbers as surrogate promary keys. However, I would enable cascading deletes to that the deletion of a parent record will delete all of tha related child records, leaving no orphans. HTH  Signature-Larry- -- > Thanks for the input/advice. I'll change the PartNumber & > UniversalPartNumber to text, that may have been my whole problem because my [quoted text clipped - 5 lines] > > Thanks again to all of you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.