2 table link in access, 3rd table needed?

[jp45678]

Hi, been trying to do this for literally hours, cannot for the life of me remember how to do it.

I have one table for Clients, just surname, forename, address, contact number etc, and I have added an ID field.

I have another called Events, where basic information on events i hold is kept, name/date etc, and i also have an Event ID field in this table.

I have setup forms to add new client to the database, and add a new event to the database.

One client can go to multiple events and obviously one event has multiple clients. I need a way of bringing up an event and then being able to select which clients were at the event or bringing up a client and linking an event or several events to their record.


I thought the way to do this was to have a third table containing ID and Event ID but i have played around with this with no success.

Its MS access 2000 on win XP.

Many Thanks

[Scott Price]

Hi, been trying to do this for literally hours, cannot for the life of me remember how to do it.

I have one table for Clients, just surname, forename, address, contact number etc, and I have added an ID field.

I have another called Events, where basic information on events i hold is kept, name/date etc, and i also have an Event ID field in this table.

I have setup forms to add new client to the database, and add a new event to the database.

One client can go to multiple events and obviously one event has multiple clients. I need a way of bringing up an event and then being able to select which clients were at the event or bringing up a client and linking an event or several events to their record.


I thought the way to do this was to have a third table containing ID and Event ID but i have played around with this with no success.

Its MS access 2000 on win XP.

Many Thanks

You are correct, you need a third linking table called tblClientEvents or whatever you want to name it. It basically will have three fields; ClientEventID Autonumber PK, ClientID Number FK, EventID Number FK.

Create your relationships between tblClients>tblClientEvents, and tblEvents>tblClientEvents. Then you write a query that pulls from this linking table based on ClientID or EventID or both...

Any questions feel free to ask!

Regards,
Scott

[jp45678]

You are correct, you need a third linking table called tblClientEvents or whatever you want to name it. It basically will have three fields; ClientEventID Autonumber PK, ClientID Number FK, EventID Number FK.

Create your relationships between tblClients>tblClientEvents, and tblEvents>tblClientEvents. Then you write a query that pulls from this linking table based on ClientID or EventID or both...

Any questions feel free to ask!

Regards,
Scott

Sorry, this might be the bit i am getting confused with, the FK's in the 3rd table, do i use the lookup wizard to link these as FK's or does the relationship window sort that out for me when i create the link?

I think the best i got so far was for the form to show me peoples name, and then there was a combo box with an event, but i could only link one event to each person. Not sure if that helps at all?

Regards

[Scott Price]

Foreign Key is just the name we humans use to refer to the Primary Key of one table that is referenced in another table. This terminology has no significance to the database itself. When you create the relationship between the tables, you are creating what we call the FK.

These relationships should have referential integrity enforced. Both Cascade Update and Cascade Delete should be required.

Now create the query that pulls the information you want to display. For example:

SELECT tblClient.ClientName, tblClientEvents.ClientID, tblClientEvents.EventID, tblEvents.EventName FROM tblClientEvents INNER JOIN (tblClients INNER JOIN tblClientEvents ON tblClientEvents.EventID = tblEvents.EventID) ON tblClients.ClientID = tblClientEvents.ClientID WHERE tblClientEvents.EventID = 1

(This sql statement is off the top of my head... what you will likely be better off doing is using the design view grid to create your query, rather than just pasting this into the sql window of a new query)

Regards,
Scott

[jp45678]

I think I've got the query done properly, I'm having problems with actually linking a client to an event. I have created the query with the columns as described.
I thought it would just be a case of creating a form using this query.
So i created the form, and it is a main form with a subform - not sure if that is correct.
The client name shows up fine, in the main form, but then in the subform contained only a blank textbox and only allowed one input, ie not allowing a second one after inputting the first.

Ideally, a combo box with a list of tblEvents.EventName would work. Is this a simple form error?

Thanks for your help

[Scott Price]

Is your subform in continuous form view? That would allow more than one entry.

Probably the simplest way is to place a combo box on the subform with row source set to

SELECT EventID, EventName FROM tblEvent ORDER BY EventName

and bound to the EventID field in your query. This will allow you to select whichever event you want from the events table.

Make the first column the bound column and set the column widths to 0", 1.5" and you will only be able to see the event name in the combo box.

Regards,
Scott

[jp45678]

I think i'm almost there. I now have a combo box containing the Events, but when i click on an Event in the list, it brings up

Object or class does not support the set of events

Thanks again

[Scott Price]

I think i'm almost there. I now have a combo box containing the Events, but when i click on an Event in the list, it brings up

Object or class does not support the set of events

Thanks again

What events do you have running for this combo box?

Do you have an AfterUpdate or BeforeUpdate expression/code/macro running? If so, what is it?

Regards,
Scott