Table relationship question

[Ann]

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

 

[Allen Browne]

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.

 

[Ann]

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.

 

[Allen Browne]

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.

 

[Ann]

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

 

[Ann]

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!

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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