Newbie question about relationships

[7daysoff]

I am trying to understand how relationships work.

Here is my example. I have a table containing 4 names called tblNames.
In it there are 4 names Bob, Carol, Ted & Alice. I have a table called
tblFoods I have 5 foods: cheese, chocolate, cookies, ham, peanuts.

Bob likes cheese, cookies and ham, Carol likes cheese and chocolate,
Ted likes cookies and peanuts. Alice likes cheese and peanuts.

First I want to create a report that shows which foods each person
likes. Would any kind person take a few minutes to walk me through the
stages of this in fairly simple steps?


Thanks very much if you can spare the time.

Bob

 

[John Vinson]

I am trying to understand how relationships work.

Here is my example. I have a table containing 4 names called tblNames.
In it there are 4 names Bob, Carol, Ted & Alice. I have a table called
tblFoods I have 5 foods: cheese, chocolate, cookies, ham, peanuts.

Bob likes cheese, cookies and ham, Carol likes cheese and chocolate,
Ted likes cookies and peanuts. Alice likes cheese and peanuts.

First I want to create a report that shows which foods each person
likes. Would any kind person take a few minutes to walk me through the
stages of this in fairly simple steps?

When you have this kind of many to many relationship, you need
*another table*. Consider this structure:

tblNames
PersonID <Primary Key, Autonumber>
FirstName
LastName

tblFoods
Food <Text, Primary Key>
<any other fields about the food, e.g. calories/serving, ...>

tblPreferences
PersonID <Long Integer, link to tblNames>
Food <Text, link to tblFoods>
<other fields about this person's liking for this food>

If Carol is PersonID 2, she'd have two records in tblPreferences:

2 Cheese
2 Chocolate
3 Cheese
3 Peanuts
<etc>

Check out the links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
especially the "Database Design 101" links.

John W. Vinson[MVP]

 

[7daysoff]

Many thanks John for taking the time to write.

If Carol is PersonID 2, she'd have two records in tblPreferences:

2 Cheese
2 Chocolate
3 Cheese
3 Peanuts

Once I'd set up the tables as you suggested and filled in the names and
foods as per my list below, I tried to create a form to input Carol's
food preferences but then things went very much awry. If you could take
me through how to create this form I'd be very grateful. If you have no
time I quite understand and thanks anyway for your help.

Bob

 

[BruceM]

A typical situation would be that you have a form (frmNames) based on
tblNames, with a subform (fsubPreferences) based on tblPreferences.
fsubPreferences would contain a combo box (bound to the Food field in the
setup John suggested) that gets its row source from tblFoods. The combo box
wizard should be able to help you with that. The combo box would let you
select from an existing list of foods, which would prevent data entry errors
(such as variant spellings) from confounding your efforts. There are
several choices by which you can make provisions for adding items that are
not on the list, should you choose.
Note that should you wish to restrict the choices to items that are already
on the list, you will need to create the records in the Foods table before
you can add them to tblPreferences by way of the subform. A simple
(separate) form bound to tblFoods would let you do that.

 

[7daysoff]

Got it. Thanks to you both for your help.

Bob