Difference between the relationships

[Guest_imported]

Hi, I have a database and I have created multiple tables, each linked by two common keys...Shift & Date. For each Date there can be three shifts so each table has shift and date. My first question is, is there any way to create a one to one relationship? I'm only able to create a one to many relationship but in some of the tables I only want one set of information. Like for example I want the user to only input one Employee. But in another table called Sales, I want the user to be able to enter as many as they wish.

My next question is the difference between the join types. How do I know which one to choose. Right now I have chosen "Include ALL records from 'Date' and only those records from 'Employee where the joined fields are equal"

The one I am unsure about is the one that says "Only include rows where the joined fields from both tables are equal"

Which one should I choose and why?

MsPeppa23

 

[LittleSmudge]

If you want a One-to-One relationship that HEAVILY implies that the two lots of data should be in the SAME table.
('Implies' is just that - not a rule - but consider it hard. )


As for joins:-

If I have a tblMyPets table with PetName as the primary key, other fields not important.

If I set up a second table called tblFavFoods, PrimeKey FoodName, ForiegnKey field PetNameRef etc

If I then join these tables ON tblMyPets.PetName = tblFavFoods.PetNameRef

Now, lets say I have lots of Pets
Some of the pets have many Favorite foods so one Pet might link to many records in the tblFavFoods - a One to Many relationship.
However, I might not know what the Favfood of my newly acquired Pooch is, so I do not populate the tblFavFoods yet for that pet.


If I use "Include ALL records from 'tblMyPets' and only those records from 'tblFavFoods' where the joined fields are equal" then I will get a complete list of my pets and risk having some of the field blank where I haven't populated the fields in tblFavFoods.

However, if I use
"Only include rows where the joined fields from both tables are equal" then I'll get an incomplete list of pets but I will get something that is quite useful as a shopping list when I go to the supermarket. Only the records in tblMyPets that have a matching record in tblFavFoods will get through.
So I can pair up PetName with Food so I buy quantity according to appetite etc. and I know what to buy as well.




'ope-that-'elps.

G LS

 

[Guest_imported]

So you would recommend that I use the "when both fields are equal" then???


Thanks

MsPepp

 

[LittleSmudge]

No.

I'd recommend that you get to grips with each type and use the one that is appropriate in the situation. Use the full range of facilities available to you.

I can't tell from the original post which will be appropriate in this instance. Only you can decide that.

I'd still like you to think hard about why you're using TWO tables and a One-to-One relationship. However, if you do need a one-to-one then create a One-to-Many and then do something on the form that will only allow one record at the many end.

If you have a "when both ends are equal" recordset before the link is made between the tables then you'll never get to see the records that need linking ( because they won't be equal yet - so they won't get through the query )

But "when both are equal" will be great for viewing existing, completed, records.



G LS

 

[Guest_imported]

I'm still not understanding the difference? Why would I want to use "when both fields are equal" or "ALL records in "Date" but only fields "Employee" that are equal." What does it mean by the "ALL records in "DATE" and also any other join type advice anyone can give...Thanks

 

[RickSpr]

The type of join ("when both fields are equal" and the like) is a separate issue from the type of relationship (one-to-one or one-to-many). What LittleSmudge is trying to say is that, while one-to-one relationships are legal, they are needed only in special cases. Most relationships are expected to be one-to-many.

The fact that you are confused about joins and relationships suggests that you don't have a lot of experience yet with relational design, so you probably don't need one-to-one relationships. (They're never absolutely required, and only experienced developers would have the finesse to recognize when they're helpful.) You probably need to reevaluate your table design. It's premature to be worrying about join types until your table design problems have been corrected.

If you'd like help with table design, it would help us if you give the names of the tables and their columns, plus the primary keys you've already created. Some background information about the real-world things the tables represent would also be helpful.

(Here's a hint about the join type: 90% of the time, you want "when both fields are equal", which is called an "inner join". You only use the other options when one or the other of the tables might not have any matching data in it.) Rick Sprague