Customers::StateID = StateRegulations::StateID
Either could be the "better design option" in different situations....
And this is a case where I would be tempted to "cheat" and use standard postal abbreviations for the state ID instead of serial numbers... But I would include a tool for updating an abbreviation in places where a given value is used just in case the powers that be decide a standard postal abbreviation should be changed...
If you have a Customer::State ID field in the customer table, then linked the relationship to the regulations(state) table via the Regulations::State ID you could have multiple regulations per state..??
Just a thought..
But I'm refining them too. Just edited my original post to better describe Option 1.
Thanks. I already setup the "cheat". Looked simpler to me.
I know this is suppose to be simple. The tables still will not synk. I'm missing something.
Customers::Home State = Regulations::Home State
Surely I have overlooked something. Any thoughts?
Do the values really match? is Home State a text field in both tables? Is Regulations::Home State an indexed field?
Correct--Matched, text field and indexed. Simplest of a database that has two tables.
List of States::States
Text fields and indexed.
I have created a sample database with limited records. Hopefully one of you smart people will find my stupid omission. Objective is to search the "States" table for a name and then click on "Back to Regulations" button and the states should match. Right? Pardon my brain freeze on this.
Link to Database below:
I see nothing in your database that actually uses the relationship that you have defined.
A portal to Regulations on the States layout would list the regulations record for that state. but to leave the states layout and see the regulations for that state doesn't happen with just a go to layout step. You'll need to use Go To Related records to take you from a record for a state to the regulations for that state on the regulations layout.
And instead of a field for regulation 1 and a second field for regulation 2. I'd have one field: Regulation and then create as many related records as need to list regulations for a given state.