one to many relation writing to Access database

[Dan Brussee]

In article <EYcMa.517516$122.49025169@amsnews02.chello.com> ,
[email]marco@alting-multimedia.nl[/email] says...

> Hi
>
> I know how to display one to many relationships between tables, but now I
> want to be able to write a record that has a one to many relationship with
> another table. What I want to achieve is the following:
>
> I have a input form which holds a dropdown list from which multiple items
> can be selected. The dropdown list is populated from a table. So if a user
> selects multiple items, how would I write that to the main table so it will
> hold the relation to the table of the list?
>

It sounds like what you want is really a many to many relationship where
a user can be related to many items, and of course an item may be
related to many users.

If this is true, you need a "link" table that associates these tables
with their unique id's.

table "items" has columns id, itemname
table "users" has columns id, username
table "items_users" has columns itemid, userid

In this case, you would add multiple records to items_users - one for
each item that the user selected from the list.

items
id itemname
1 Monitor
2 Case
3 Keyboard

users
id username
1 mmouse
2 gnoir
3 tjones

items_users
itemid userid
1 1
1 2
2 1
3 2
3 3

In this example:
User mmouse has a Monitor and a Case
User gnoir has a Monitor and a Keyboard
User tjones has a Keyboard


--

Remove NOT from email address to reply. AntiSpam in action.

[Andrew Durstewitz]

Very well put Dan, I second your thought.

-Andrew

* * * Sent via DevBuilder [url]http://www.devbuilder.org[/url] * * *
Developer Resources for High End Developers.

[Marco Alting]

Thanks for the quick answer. While reading it I think I never had to
build a many to many relation. I'm now wondering what the SQL would look
like for both writing the information to the tables and to display the
information again from the tables...

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!