Configuring primary key (1 Viewer)

[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

 

[Pat Hartman]

The three tables should look something like:

tblState
StateID (autonumber pk)
StateCode
StateName

tblCounty
CountyID (autonumber pk)
StateID (foreign key to tblState)
CountyName

tblMunicipality
MunicipalityID (autonumber pk)
CountyID (foreign key to tblCounty)
MunicipalityName

 

[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

 

[Pat Hartman]

MunicipalityID is the field that will be stored in your other tables. State and County can be inferred so they are not stored in your other tables.

It is very important to enforce RI on relationships. If you don't do it, you leave yourself open to having bad data in your tables.

 

[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

 

[Pat Hartman]

Use combos on your forms. If you let the wizard build the combo, one option will be to hide the key field. If you do the combo yourself, these are the properties you need to set correctly.
on the Format tab:
ColumnCount = the number of columns in the RowSource
ColumnWidths = the width of each column. Use 0 for columns that you wish to hide such as the "foreign key".
ListWidth = controls the width of the list. Auto sets the width to the width of the combo itself.
on the Data tab:
ControlSource = the name of the column in which the "foreign key" will be saved.
RowSource = the name of the query (or table) that selects the list data.
BoundColumn = The "foreign key" column which is usually column 1.
LimitToList = Set to yes if you want to restrict selections to values already in the list.

To get the "text" value to show in your reports, create a query that joins the main table to the lookup table. Select the "text" column from the Lookup table rather than the "foreign key" column from the main table.

 

[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

 

[Pat Hartman]

Why would it be difficult? Joins are the glue of a relational application. You normalize your data to eliminate redundancy and prevent data anomolies. You then use queries that join the various tables to turn the data back into information for reporting purposes.