Multiple Charity Events to ONE Contacts

[Carl Morris]

Hi all,

I have already created a Contacts table, and a separate Events table.

We have charity events throughout the year that are dependant on volunteers helping to make it happen.

Basically, I have fields in the Events table such as Event_Name, Event_Date, Event_Time_Start, Volunteers_Required etc etc..

What I want to do, is if a venue (whose details are in the Contacts) has agreed to let us have an event, we would like to link the event to that contact - if it was just one event per contact, it wouldn't be a problem, but because one contact can have more than one event, this is causing me a problem and I don't know the best way to go about it as I need to keep records on each event individually (i.e. the two events could have a different set of volunteers, times etc).

I am sorry my description is not as good as it can be - I know what I mean and what I need, but to explain in words is difficult.. (I am deaf so my vocabulary range is not as great as most people's and I have difficulty explaining sometimes..)

Would it help if I put our file online so you can have a look as then you would see what I mean..?

Any questions - please do ask..

Thanks in advance,

Carl

[bcooney]

Hi Carl,

Sounds like you just need a join table between Events and Contacts, "EvCon." This table will store the foreign EventID and ContactID. You can place a portal to this table on both the Event and Contact form views.

Why are Venue details in a Contact record? Why not a Venue table (that can have related contacts--another join btw Venue and Contacts).

[comment]

if it was just one event per contact, it wouldn't be a problem, but because one contact can have more than one event, this is causing me a problem

How many venues can one event have?

[Carl Morris]

Hi guys..

I think I had better try and build a better picture of what I am doing, so that you can see what I am trying to achieve, as I don;t think I did a very good job in the first post?

I already have a contact ID for the contacts, and also for the events.

You made a good point - it will probably be a many to many as for example;

• Drake Circus (Venue) has three events coming up in the next 9 months on three different dates (two pool events and one collection)..

• Each event will have volunteers, so "Fred Tandy" can volunteer at more than one

• Each venue can have more than one event, and one event can have more than one contact

It's a little baffling - I have a great layout, but I might have set it up all wrong?

I have 5 tables;

• Correspondence History (works fine)

• CPE_Events (Contains all the event details such as Event_Date_Start, etc - 82 fields)

• General_Contact_Info - For all contacts, including venues as many venues are also my contacts, and there's just one person per venue who I must contact - 254 fields

• Role_Description - Just for out volunteer roles (works fine)

The trick, obviously, is to get the whole thing working well together as we will need to retain a lot of information..

Basically, we have many events going on, and we are always appealing for volunteers, so we need the ability to keep track of all the events and volunteers as well as being able to send appeals/newsletters etc out to the right people.

I started off well, but may have lost my way a little!

PS - Where are you based? I know some people speak via telephone to discuss things, but I can't do that what with being deaf, but I would be willing to travel to listen to the experts' advice. I am based in Stoke on Trent..

[comment]

You haven't answered my question. If an event is held in a single venue, I would add a VenueID to the Events table and populate it with the ContactID of the venue (unlike Barbara, I see no problem with venues being in the Contacts table).

For the volunteers, you need a join table between them and Events (it is a many-to-many relationship). In the join table, you can assign each volunteer a role in the specific event. Note that one of the roles could be "venue provider" - but I am not sure that would be as convenient as having a separate relationship for this purpose.