Table relationship question

[Dave Lambert]

I'm using Access 2016 (Office 365).


My database has 3 tables: Producer, States, and Insureds.
I need a 1-to-many relationship from the States table to the Producer table.
I need a 1-to-many relationship from the States table to the Insureds tables.
I need 1-to-many relationship from the Producer table to the Insureds table.

I have a 1-to-many relationship from the States table to the Insureds table. This is on the State Abbr field of the States table and the State field in the Insureds table.

I also have a 1-to-many relationship from the Producers table to the Insureds table. This is on the Producer field in each table.

When I try to create a 1-to-many relationship from the States table to the Producer table I can’t get it done. Access keeps trying to create a 1-to-1 relationship. Why?

Each file has an autonumber primary key.

I don’t want to put Key values of the states into the Producers and Insureds tables; I want the actual data (State Abbr and the Producer) to be in the tables.

The fields I’m trying to put the relationships on are not the primary key fields.

I do not have a lookup table defined. Do I need a lookup table?

[June7]

A relationship must include primary key. If you want to save the text values then don't set autonumber as primary key. The text field is the key.

[orange]

Dave,

Your relationships are determined by the business rules/facts of the "business".
So saying

I don’t want to put Key values of the states into the Producers and Insureds tables; I want the actual data (State Abbr and the Producer) to be in the tables.

is a little like cart before the horse situation in my view.

Can you describe to readers in 4 -5 lines of simple, plain English what Producers, Insureds and States mean in your business?
Best to use simple terms, no jargon, so we understand what you are dealing with.

Here is a link to various info on Database Planning and Design. You should research Normalization to help understand table design.

Good luck with your project.

[ssanfu]

I do not have a lookup table defined. Do I need a lookup table?

Nope. My rule is "NEVER use look up FIELDS in Tables!"

----------------------------------------------------------------------------------------------------------------

I don’t want to put Key values of the states into the Producers and Insureds tables; I want the actual data (State Abbr and the Producer) to be in the tables.

I can understand about the State abbr because the states are very stable name wise.
But I would recommend using the PK/FK (number) fields.
Why do you want to store the agent's name (Producers)?

If I understood the requirements correctly, the design might look something like this



----------------------------------------------------------------------------------------------------------------

I would design the tables/relationships more like this (normalized design):

[Dave Lambert]

Hi al!

Thanks for the replies! They are greatly appreciated.

I'm in the logistics department of an agricultural products company. I'm trying to set up a small database where I can keep track of when insurance coverage is due to expire for the policyholders (Insureds) that haul shipments for us. If coverage expires the policyholder(s) cannot haul freight for the company. I'm wanting to create queries/reports that show freight haulers with insurance coverage that will be expiring in the near future (maybe 1 to 2 weeks). This should help us to better keep track of these situations.

I'm using 3 tables:
Producers (Insurance Agents, Agencies or Companies)
Insureds (Policyholders that purchase insurance from the Producers)
States (The state abbreviations and full names)

A producer can have many insureds. (one-to-many).
A state can have many producers (one-to-many).
A state can be in many insureds addresses (one-to-many).
An insured/policyholder can have multiple insurance policies through multiple producers in which specific coverages expire at different times.
Each producer and insured have an address which includes the state. The state is the one side to a one-to-many relationship. The producers and insureds are the many side of that one-to-many relationship.

I've received some good advice already and am going to read up on those articles and do a bit of redesign.

[orange]

This seems to suggest a Many to Many relationship

An insured/policyholder can have multiple insurance policies through multiple producers in which specific coverages expire at different times.

Can you mock up a few records (dummy data) for sample test data?
How much info(multiple insurance policies ) re Policy do you maintain/need?

[ssanfu]

A producer can have many insureds. (one-to-many).
A state can have many producers (one-to-many).
A state can be in many insureds addresses (one-to-many).
An insured/policyholder can have multiple insurance policies through multiple producers in which specific coverages expire at different times.
Each producer and insured have an address which includes the state.

Well, that makes a difference.
I would agree with orange - would need a junction table (for the Many to Many relationship).