one-to-many - how to display data from two tables?

[Nathon Jones]

Hi,
I'm working with Access/VBScript/ASP.

I have an events table that has:

eventID
venueID
eventname

....I also have a venues table that has

venueID
venuename

How do I create a recordset on a page, that will display the events, but
will also display the venue name, by matching the venueID in the events
table, with the venueID in the venues table?

Got me stumped.

Thanks.
Nath.

[greekchild]

select events.eventid, events.venueid, events, eventname, venue.venueid, venue.venuename
from events, venues
where events.venueid=venues.venueid

[greekchild]

select events.eventid, events.venueid, events, eventname, venue.venueid, venue.venuename
from events, venues
where events.venueid=venues.venueid

[Julian Roberts]

Create a query in Access, it's got some wonderful wizards and tools for
building queries.

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[Nathon Jones]

Hi,

Yup, I decided that I would do the query in Access rather than server side.

Thanks for the advice though. One other thing...

I have an events table which has the following

eventID
eventname

....I have an artists table that has the following:

artistID
artistname

Some of the events have more than one artist appearing. Similarly, some of
the artists appear at more than one event.

So how do I create a query/relationship that will reflect this?

Do I need to create an artistID field in my events table? If so, then how
do I make multiple selections in that field (considering that more than one
artist might appear at any one event).

My problem is the same the other way around....if I create an eventID in the
artists table, then how do I do the multiple selection?

Thanks again,

Nath.


"Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
news:cvhuf2$52u$1@forums.macromedia.com...

> Hi,
> I'm working with Access/VBScript/ASP.
>
> I have an events table that has:
>
> eventID
> venueID
> eventname
>
> ...I also have a venues table that has
>
> venueID
> venuename
>
> How do I create a recordset on a page, that will display the events, but
> will also display the venue name, by matching the venueID in the events
> table, with the venueID in the venues table?
>
> Got me stumped.
>
> Thanks.
> Nath.
>

[Michael Fesser]

.oO(Nathon Jones)

>I have an events table which has the following
>
>eventID
>eventname
>
>...I have an artists table that has the following:
>
>artistID
>artistname
>
>Some of the events have more than one artist appearing. Similarly, some of
>the artists appear at more than one event.

So you have a m:n (many-to-many) relationship.

>So how do I create a query/relationship that will reflect this?

You need a third table to connect both others. Assuming eventID and
artistID are the primary keys, the third table would simply contain
these both fields:

eventID
artistID

For every combination of artist/event you simply insert a record into
that table. Querying the three tables can be easily done with two
(INNER) JOIN clauses.

Micha