Many-to-many relationship in Access

  coolteentom 17:17 21 Oct 2010


I am creating a relatively simple database for a arts organisation I play for in London. I'm very rusty on databases and I was just wondering if anyone could confirm that I am going about this the right way?

The first table I am linking is 'Musicians'.
The second table is 'Events'.

I hope to be able to create an invoice option that breaks down how much each musician was paid at each event. One musician can attend multiple events, and one event can involve multiple musicians. So, I have created a linking table 'Payroll' which holds Musician ID, Event ID and payment information for that specific link.

Hope this makes sense - is it correct?

Many thanks,Tom

  Woolwell 18:40 21 Oct 2010

Sounds ok. Assume that you will use a query to extract the information. Rather rusty myself!

  coolteentom 01:05 22 Oct 2010

Thanks Woolwell, I'll give it a shot then and see if it works out! And yeah, I'll be using a query once the relationship has been set up.

  skeletal 13:00 22 Oct 2010

Yes, you are on the right lines. You can’t usually have a “many to many” relationship directly. One strange version is a “Cartesian join” (one link that explains it: click here)
but this is likely to be very specialised or a mistake!

For what you are trying to do, a link table is correct, and as Woolwell says, you can then play tunes with your query, or queries.

Also, it could be that you have many events on the same day. You may need to think about having a date table also linked into the mix.


  Woolwell 13:38 24 Oct 2010

I had another thought about this. The event is a one off and took place on one date. So the relationship is one to many (event to musician). You should be able to construct a parameter query which will show which events a musician played at and for how much.

You should also be able to create a report which lists each event, the musicians that took part and how much each was paid.

I think this is perhaps simpler than what you are trying to do.

  Woolwell 13:38 24 Oct 2010

ps each event is unique by date and time and title.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone