One to One query vs One to Many query question

[xPaul]

Morning all,

I'm not sure if it's because I'm tired, or my google fuu isn't working... however can someone explain the following:

I have three tables, two relationships - one is a one to many and the other is a one to one. From this I have created two relationships to join data between the two tables.

When I create the query using the one to many relationship it pulls the related data from the related table when I create a new record using the PK joined between both. However when I do the same but using the one to one relationship it does not automatically fill in this detail for me, why is this?

Table example:

Person
First Name | Last Name | UniqueID

License
License Name | UniqueID

Car
Car Make | Unique ID

So, when I create a relationship between Person and License using all fields from both (a one to one relationship) using all fields and create a new record it does not automatically populate First Name, Last Name.

Whereas when I create a relationship between Person and Car using all fields from both (a one to many relationship) and create a new record it will automatically pull First Name and Last Name from the Person table.

Why is this?

This of course is a made up scenario, I'm just trying to understand....

 

[PlayMisty]

This One to One maybe wrong. It most likely a One to Many and therefore you have your Primary Keys and Foreign Keys set up wrong.

Can you post a pick of your relationships so I can see better

 

[gemma-the-husky]

which is the one to one?

a 1:1 relationship is NEVER EVER required. It may be useful in some special cases, but is never a requirement

 

[xPaul]

So, for ease of explaining:

I have attached an example database.

• Open Person/Car query (one person to many cars)
• Add a new record, select PersonID 3 - First Name and Last Name populates
• Open Person/License query (one person to one license)
• Add a new record, select PersonID 3 - First Name and Last Name do not populate

I wish to understand why this is... not necessarily to fix relationships to suit - apologies for the abruptness

 

[PlayMisty]

PersonID is unique. Remove this and it may fix things. Not guaranteed.

The overall problem is you Table designs.

eg A One to One is really ONE table. Get rid of one of the tables and place all the data in same table.

This should leave you with two "One to Many tables."

 

[PlayMisty]

So, for ease of explaining:

I have attached an example database.

• Open Person/Car query (one person to many cars)
• Add a new record, select PersonID 3 - First Name and Last Name populates
• Open Person/License query (one person to one license)
• Add a new record, select PersonID 3 - First Name and Last Name do not populate

I wish to understand why this is... not necessarily to fix relationships to suit - apologies for the abruptness

Are we getting anywhere.

Sing out if you want more help.