By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

2 table link in access, 3rd table needed?

P: 4
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
Sep 13 '07 #1
Share this Question
Share on Google+
7 Replies


Scott Price
Expert 100+
P: 1,384
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
Sep 13 '07 #2

P: 4
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
Sep 13 '07 #3

Scott Price
Expert 100+
P: 1,384
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Sep 13 '07 #4

P: 4
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
Sep 13 '07 #5

Scott Price
Expert 100+
P: 1,384
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
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 13 '07 #6

P: 4
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
Sep 14 '07 #7

Scott Price
Expert 100+
P: 1,384
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
Sep 14 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.