 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / February 2006Newbie 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 >I am trying to understand how relationships work. > [quoted text clipped - 8 lines] >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] 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 > >I am trying to understand how relationships work. > > [quoted text clipped - 39 lines] > > John W. Vinson[MVP] 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. > Many thanks John for taking the time to write. > [quoted text clipped - 56 lines] >> >> John W. Vinson[MVP] Got it. Thanks to you both for your help. Bob |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.