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
|
|||
|
|||
Table relationship question
Hello -
I am a new user trying to design a database. I need some direction on how to link my tables together. What I am trying to accomplish is the following: I need to link a user with their interests. I need to create a form that shows the user information (name, address, etc...) and a subform that has a drop-down menu with a list of interests for the user to choose. They should be able to choose more than one interest and have those interest choices be stored in the User and Interests table. My first problem is that I'm not sure how to link the tables together to make this happen. My second problem is that I'm not sure how to allow for multiple interest choices in the subform. I will eventually need to create a query that shows which user has which interests. For example, user#1's interests are swimming, bowling and skiing. Below are my tables/fields. Can anyone provide any guidance? Please be as specific as possible as I'm new to this! Thanks! My first table (Users) has the following fields: UserID (primary key) UserFirstName UserLastName User Address My second table (Interests) has the following fields: InterestID (primary key) InterestName My third table (Users and Interests) has the following fields: UserInterestID UserID |
#2
|
|||
|
|||
Ann, the structure you suggest is spot on.
Main form: bind to Users table. Subform: bind to [Users and Interests] table. The subform will contain a combo. RowSource for the combo will be the Interests table. Make sure the subform is in Continuous or Datasheet view, and set its LinkMasterFields/LinkChildFields to UserId. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ann" wrote in message ... I am a new user trying to design a database. I need some direction on how to link my tables together. What I am trying to accomplish is the following: I need to link a user with their interests. I need to create a form that shows the user information (name, address, etc...) and a subform that has a drop-down menu with a list of interests for the user to choose. They should be able to choose more than one interest and have those interest choices be stored in the User and Interests table. My first problem is that I'm not sure how to link the tables together to make this happen. My second problem is that I'm not sure how to allow for multiple interest choices in the subform. I will eventually need to create a query that shows which user has which interests. For example, user#1's interests are swimming, bowling and skiing. Below are my tables/fields. Can anyone provide any guidance? Please be as specific as possible as I'm new to this! Thanks! My first table (Users) has the following fields: UserID (primary key) UserFirstName UserLastName User Address My second table (Interests) has the following fields: InterestID (primary key) InterestName My third table (Users and Interests) has the following fields: UserInterestID UserID |
#3
|
|||
|
|||
Allen, thanks for the help. One more question: how do I link the tables
together? Do I link them by their primary keys? Thanks again. "Ann" wrote: Hello - I am a new user trying to design a database. I need some direction on how to link my tables together. What I am trying to accomplish is the following: I need to link a user with their interests. I need to create a form that shows the user information (name, address, etc...) and a subform that has a drop-down menu with a list of interests for the user to choose. They should be able to choose more than one interest and have those interest choices be stored in the User and Interests table. My first problem is that I'm not sure how to link the tables together to make this happen. My second problem is that I'm not sure how to allow for multiple interest choices in the subform. I will eventually need to create a query that shows which user has which interests. For example, user#1's interests are swimming, bowling and skiing. Below are my tables/fields. Can anyone provide any guidance? Please be as specific as possible as I'm new to this! Thanks! My first table (Users) has the following fields: UserID (primary key) UserFirstName UserLastName User Address My second table (Interests) has the following fields: InterestID (primary key) InterestName My third table (Users and Interests) has the following fields: UserInterestID UserID |
#4
|
|||
|
|||
In the Relationships window (Tools | Relationships), drag:
- UserId from the User table onto UserId in the [Users and Interests] table. - InterestId from the Interests table onto InterestId in the [Users and Interests] table BTW, just noticed you had a UserInterestId field in the [Users and Interests] table? That should be an InterestId field (foreign key, not primary key.) The primary key of [Users and Interests] table will be the combination of UserId + InterestId (i.e. select both fields in table design view, and press the Key icon on the toolbar.) Using the combination of the 2 fields as primary key means that you cannot enter the same UserId and InterestId combination multiple times in [Users and Interests] table. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ann" Ann @discussions.microsoft.com wrote in message ... Allen, thanks for the help. One more question: how do I link the tables together? Do I link them by their primary keys? Thanks again. "Ann" wrote: Hello - I am a new user trying to design a database. I need some direction on how to link my tables together. What I am trying to accomplish is the following: I need to link a user with their interests. I need to create a form that shows the user information (name, address, etc...) and a subform that has a drop-down menu with a list of interests for the user to choose. They should be able to choose more than one interest and have those interest choices be stored in the User and Interests table. My first problem is that I'm not sure how to link the tables together to make this happen. My second problem is that I'm not sure how to allow for multiple interest choices in the subform. I will eventually need to create a query that shows which user has which interests. For example, user#1's interests are swimming, bowling and skiing. Below are my tables/fields. Can anyone provide any guidance? Please be as specific as possible as I'm new to this! Thanks! My first table (Users) has the following fields: UserID (primary key) UserFirstName UserLastName User Address My second table (Interests) has the following fields: InterestID (primary key) InterestName My third table (Users and Interests) has the following fields: UserInterestID UserID |
#5
|
|||
|
|||
Hi Allen -
Thank you again for all of your help. Do you mean that my third table [Users and Interests] should look like this? UserandInterestID (primary key) InterestId UserID What field from my other tables should I link to these fields in this table? Sorry I'm so confused! Thanks. "Ann" wrote: Hello - I am a new user trying to design a database. I need some direction on how to link my tables together. What I am trying to accomplish is the following: I need to link a user with their interests. I need to create a form that shows the user information (name, address, etc...) and a subform that has a drop-down menu with a list of interests for the user to choose. They should be able to choose more than one interest and have those interest choices be stored in the User and Interests table. My first problem is that I'm not sure how to link the tables together to make this happen. My second problem is that I'm not sure how to allow for multiple interest choices in the subform. I will eventually need to create a query that shows which user has which interests. For example, user#1's interests are swimming, bowling and skiing. Below are my tables/fields. Can anyone provide any guidance? Please be as specific as possible as I'm new to this! Thanks! My first table (Users) has the following fields: UserID (primary key) UserFirstName UserLastName User Address My second table (Interests) has the following fields: InterestID (primary key) InterestName My third table (Users and Interests) has the following fields: UserInterestID UserID |
#6
|
|||
|
|||
Yes, you could create the table with the 3 fields like that.
Presumably you intend UserAndInterestId field as an AutoNumber. InterestId will be a field of type Number (size Long), and will relate to the InterestId field in the Interests table. UserId will be Number (Long), related to UserId in the Users table. (Actually, I'm not sure that's a good name for a table, because has a collection named Users. Perhaps name it tblUser.) That would work okay, but you might decide that the UserAndInterestId field is not needed. You could make the combination of InterestId + UserId your primary key if you prefer. It's not really important, but it would have the benefit of preventing anyone entering the same User + Interest combination multiple times. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ann" wrote in message ... Hi Allen - Thank you again for all of your help. Do you mean that my third table [Users and Interests] should look like this? UserandInterestID (primary key) InterestId UserID What field from my other tables should I link to these fields in this table? Sorry I'm so confused! Thanks. "Ann" wrote: Hello - I am a new user trying to design a database. I need some direction on how to link my tables together. What I am trying to accomplish is the following: I need to link a user with their interests. I need to create a form that shows the user information (name, address, etc...) and a subform that has a drop-down menu with a list of interests for the user to choose. They should be able to choose more than one interest and have those interest choices be stored in the User and Interests table. My first problem is that I'm not sure how to link the tables together to make this happen. My second problem is that I'm not sure how to allow for multiple interest choices in the subform. I will eventually need to create a query that shows which user has which interests. For example, user#1's interests are swimming, bowling and skiing. Below are my tables/fields. Can anyone provide any guidance? Please be as specific as possible as I'm new to this! Thanks! My first table (Users) has the following fields: UserID (primary key) UserFirstName UserLastName User Address My second table (Interests) has the following fields: InterestID (primary key) InterestName My third table (Users and Interests) has the following fields: UserInterestID UserID |
#7
|
|||
|
|||
Allen...thank you so much. You have been extremely helpful. I'm going to
try it and let you know how it goes! Thanks again! "Allen Browne" wrote: Yes, you could create the table with the 3 fields like that. Presumably you intend UserAndInterestId field as an AutoNumber. InterestId will be a field of type Number (size Long), and will relate to the InterestId field in the Interests table. UserId will be Number (Long), related to UserId in the Users table. (Actually, I'm not sure that's a good name for a table, because has a collection named Users. Perhaps name it tblUser.) That would work okay, but you might decide that the UserAndInterestId field is not needed. You could make the combination of InterestId + UserId your primary key if you prefer. It's not really important, but it would have the benefit of preventing anyone entering the same User + Interest combination multiple times. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ann" wrote in message ... Hi Allen - Thank you again for all of your help. Do you mean that my third table [Users and Interests] should look like this? UserandInterestID (primary key) InterestId UserID What field from my other tables should I link to these fields in this table? Sorry I'm so confused! Thanks. "Ann" wrote: Hello - I am a new user trying to design a database. I need some direction on how to link my tables together. What I am trying to accomplish is the following: I need to link a user with their interests. I need to create a form that shows the user information (name, address, etc...) and a subform that has a drop-down menu with a list of interests for the user to choose. They should be able to choose more than one interest and have those interest choices be stored in the User and Interests table. My first problem is that I'm not sure how to link the tables together to make this happen. My second problem is that I'm not sure how to allow for multiple interest choices in the subform. I will eventually need to create a query that shows which user has which interests. For example, user#1's interests are swimming, bowling and skiing. Below are my tables/fields. Can anyone provide any guidance? Please be as specific as possible as I'm new to this! Thanks! My first table (Users) has the following fields: UserID (primary key) UserFirstName UserLastName User Address My second table (Interests) has the following fields: InterestID (primary key) InterestName My third table (Users and Interests) has the following fields: UserInterestID UserID |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unable to edit records in a form or query | Merlin | Using Forms | 7 | May 10th, 2005 02:00 PM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Table and Relationship design problem | douglas jones | Database Design | 2 | March 16th, 2005 11:45 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |