multiple selection on ID's? Is this possible?

[Nathon Jones]

Hi,

Hoping for some expert guidance here...

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?

Hope someone can help because it's starting to give me a headache!

Thanks.
Nath.

 

[Sprinks]

Hi, Nathon.

First, re: your headache--take a deep breath and hum "Om".

The relationship between Artists and Events is a many-to-many relationship.
This can be implemented by an intermediate table, which is in a one-to-many
relationship with both Artists and Events. For purpose of discussion, call
it ArtistEvents:

ArtistEvents
----------------------
ArtistEventID AutoNumber (Primary Key)
EventID Number (Foreign Key to Events)
ArtistID Number (Foreign Key to Artists)

For data entry, you can create two forms, one based on Artists, one on
Events, each with a continuous subform based on ArtistEvents (it could be the
same one). The first would be linked by the ArtistID, the second by the
EventID. This would either let you enter an artist's upcoming gigs or enter
a list of performers for a certain event.

Hope that helps.
Sprinks

 

[Nathon Jones]

Hi Sprinks,
Thank you for that.

The ArtistEvents table...can this be a query?
I already have data inputted into both the Artists and Events tables...am I
going to have to re-enter this into ArtistEvents?

Can you explain Intermediate tables for me a little?

My confusion is...what else is contained in this intermediate table, if I
already have all the data in the Artists and Events tables?

Thanks again. Your guidance is very much appreciated.
Nath.

 

[Sprinks]

Hi, Nathon.

The Artists table captures attributes of the artist -- his/her name, phone,
birthdate, website, etc.

The Events table captures the same for each event -- its name, date,
location, promoter, etc.

The ArtistEvents table cannot be a query. It is a necessary table to model
the many-to-many relationship between Artists and Events--it captures the
fact that *this* artist is performing at *this* event (or, alternatively,
that *this* event has *this* artist performing at it). In this table, do NOT
duplicate the event name or the artist name or any other fields from these
tables--this table stores only the foreign keys to the Events and Artists
tables to associate the artist with the event.

Now, to print a list of the performers playing at a certain event, or the
events an artist is scheduled to play, you will need fields from the Artists
and Events table--the names, dates, etc. For this, create a query consisting
of all three tables.

Sample Report Output

Event: Woodstock

Scheduled Artists:
-------------------------------------------
Jimi Hendrix
Crosby, Stills, Nash and Young
Janis Joplin
Country Joe McDonald & The Fish
etc.

"Jimi Hendrix" is a name stored in the Artists table, but the fact that he's
playing Woodstock is stored in the intermediate table:

Sample Artist Record:
ArtistID 5
ArtistName Jimi Hendrix

Sample Event Record:
EventID 31
EventName Woodstock

Sample ArtistEvent Record:
ArtistEventID AutoNumber
ArtistID 5
EventID 31

Does that make sense?

In implementing this many-to-many relationship in forms, I'd create 2 main
forms, based on Artists and Events, respectively. Contrary to what I said
earlier, however, you will need two different subforms.

The one to be inserted on the Artists form needs only a combo box for the
EventID, while the one for the Events form needs only a combo box for the
ArtistID. You can set them up such that they *display* the more meaningful
name field, but they *store* the foreign key in the underlying ArtistEvent
table.

The easiest way to do this is with the wizard enabled (View, Toolbox, and
toggle on the button with the wand and stars). Select both the ID and Name
fields, accept the "Hide Key Field" default, and tell it to store the
selection in the appropriate underlying ArtistEvent field.

Hope that helps.
Sprinks

 

[Nathon Jones]

Hi Sprinks,

Thank you. The explanation, using Woodstock, was most useful.

I've set up the intermediate table, but this is now causing me serious
problems with duplicate entries in my Events.

Because, lets say 2, artists appear at one event, this is causing the event
to be listed twice! It's basically repeating the Event information for
every artist who appears at that event. This is really confusing me.

I wanted to list events, and use one of the artist fields (which references
an image), to display alongside the event. The minute I do that, I get
duplicate entries in Events.

Really sorry about my ignorance - I've worked with graphic design and web
site design for ages and haven't even delved into the real usefulness of
databases - tables and simple queries I can do. Intermediate tables,
many-to-many relationships and I'm completely lost.

Nath.

 

[Sprinks]

Hi, Nathon.

Please post the table structures of your three tables, i.e., the fieldnames
and fieldtypes of each.

Where are you seeing duplicates of the Events? If it is on a form, please
post the RecordSource and describe the form. If the form has a subform,
please post the LinkChildFields and LinkMasterFields properties, the
RecordSource of the subform, and the ControlSources of each control.

If you go into the Events table directly, do you see an event listed twice?

What *should* happen is that each event should have exactly one record in
Events, and each Artists should have exactly one record in Artists.
ArtistEvents, however, may have multiple records for the same event or
multiple records for the same artist.

Sample Records in ArtistEvents

EventID ArtistID
------------ ------------
5 3
5 24
5 16
6 5
6 3

If this data was displayed on a main form based on Events, and a subform
based on ArtistEvents as described earlier with a single combo box for the
ArtistID, the main form would display one event at a time. For example, if
event 5 was displayed, the main form would show the event name, and the
subform would display the names of artists 3, 24, and 16.

Hang in there; we'll get it.
Sprinks