i am creating a small database with tables: bars, restaurants, clubs and hotels in a particular city. I have set an ID/autonumber to each entry in all tables. Although i dont want a number to duplicate in any of the tables. i set it to no duplication and the relationship is set AND STILL it does not work. can anyone help me?
Tags: Access, Autonumber, Bars Clubs, Duplicate, Hotels, Ms Access, Primary, Primary Key, Relationship, Restaurants, Tables
The “No duplication” control only applies to that table, not the whole database of several tables. If you set the ID for each of the tables to Autonumber, it will always start at 1 (unless you format it to start at a different numer) and you will get ID numbers duplicated in each table but as you will give each of the ID fields a different name in each table, the database will not see them as duplications.
I would put all the bars, restaurants etc in the same table and create a category field in which you identify each record as either a bar, restaurant etc then they will all have different ID numbers in the same table. It will be easier to handle and extract data
Setting a primary key as no duplicates only stops it duplicating in the same table. I can’t think of any circumstances where you would need to do what you are trying to do unless there is a flaw in the design of the database.