If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Newbie question about relationships
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 |
#3
|
|||
|
|||
Newbie question about relationships
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 John Vinson wrote: On 8 Feb 2006 20:00:38 -0800, wrote: 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 structu 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/conrad...resources.html especially the "Database Design 101" links. John W. Vinson[MVP] |
#4
|
|||
|
|||
Newbie question about relationships
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. wrote in message oups.com... 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 John Vinson wrote: On 8 Feb 2006 20:00:38 -0800, wrote: 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 structu 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/conrad...resources.html especially the "Database Design 101" links. John W. Vinson[MVP] |
#5
|
|||
|
|||
Newbie question about relationships
Got it. Thanks to you both for your help.
Bob |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Newbie Question: MS Access - Website Interactions | Nick | New Users | 1 | June 27th, 2005 10:49 PM |
Newbie question on calculated fields | Nick Xylas | General Discussion | 3 | June 7th, 2005 05:08 PM |
newbie new selfcert/macro question 2 | KR | Powerpoint | 3 | May 19th, 2005 08:40 PM |
Relationships cardinality and . . (Newbie) | Lionel | New Users | 3 | September 26th, 2004 06:29 AM |
Q: newbie data access page question | MarkD | General Discussion | 1 | July 7th, 2004 10:05 PM |