Configuring primary key

[myhnews]

I have a tblState, every state can have multiple Counties, and every County can have multiple Municipalities
This should be a One to Many to Many, right????

However, several states might have the same County name

In order to resolve this, should I add 2 primary Keys in the tblCounty????

If so, I already tried and couldn’t configure it.

I have attached a sample DB
You can open the relationship view for a better explanation

I appreciate any kind of support

Thank you
Joe

 

[neileg]

It's a better idea to use autonumbers as primary keys. That way you can have duplicated names without them being duplicate records.

 

[bat1799]

in tblCounty you would add the primary key from tblState as a forign key

Peter

 

[myhnews]

I appreciate you help.

I’m just concerned about using AutoNumbers for the Primary Key. Because, if I use AutoNumbers and store only the AutoNumbers not the value, if a record on the state, county, or municipality tables will be deleted accidentally, and will be re-entered, it will get a new AutoNumber, and will not match the existing records on the sub tables.

I would like to have you opinion on this.

Thanks
Joe

 

[Bat17]

set up relational integrity and Access won't let you delete records that will create 'orphans'

HTH

Peter

 

[myhnews]

I also use tables where it cannot be setup with relational integrity
What should I do?

 

[Bat17]

The tables that Pat gave you will all lock so they are OK.
Main thing you can do is to lock people out of tables and only let them use forms. That way you have much more control

Peter

 

[myhnews]

I have revised the sample DB to include auto numbers for all primary keys

However, I did not add auto number for the state ID, the primary key will be the 2 digit sate name.

However, I still need your help.

In order to fix the duplication problem, I will have to store the CountyID instead the County name.
So I changed the county field on the order form to include the CountyID. However, after selecting the county, I would like the user to see the County name instead of CountyID.

The same applies to the report.

I have attached the new file

I appreciate you help
Thanks
Joe

 

[myhnews]

Thank you
Joe

 

[myhnews]

Good morning everyone,

The county field is being used on several reports and forms. Some reports are Union and some are a combination of 3 tables. To add the additional tblCounty might be difficult. Is there any other what to leave the reports source the same and just configure the County fields to display the county name?

Please advise
Thanks

Joe