displaying many to many linked tables

jcolles
jcolles used Ask the Experts™
on
I am writing a database for a charity that has contacts some of whom attend events and where they may pledge or make donations to the charity. The attached relationship diagram shouws my basic design.  
I am trying to display a contact form with a subform showing an events datasheet so that an event can be  selected. I have used a query  (SQL code below), but it always remains empty as all conditions are not satisfied until the data has been entered.
I am going round in circles, please can someone help!!
SELECT Contacts.CustomerID, ContactEvent.ConteventCustID, ContactEvent.EventID, ContactEvent.Invited, ContactEvent.Accepted, ContactEvent.Attended, ContactEvent.[Amount Pledged], ContactEvent.donationID AS ContactEvent_donationID, Events.EventID, Events.EventName, Events.Location, Events.Date, Events.Cost, Events.Comments
FROM Contacts INNER JOIN (ContactEvent INNER JOIN Events ON ContactEvent.ConteventCustID = Events.EventID) ON Contacts.CustomerID = ContactEvent.ConteventCustID;

Open in new window

relationships.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your query contains "ContactEvent.ConteventCustID = Events.EventID" as link, while your diagram suggests "ContactEvent.EventID = Events.EventID", but that isn't the point.

The main interface solution to display one-to-many relationships is a form with a subform; the subform showing the "many" side. The main interface solution for many-to-one relationships is a combo box. Hence, many-to-many relationships can be viewed as a form with a subform, itself containing a combo.

* Create a form on Contacts. Just the table, not a query.
* Insert a sub-form showing ContactEvent.
* Link the main form to the subform using the key fields
* For the field EventID, display a combo box, based on Events

When you navigate to a contact without events, the subform will be empty. But the "new" record will be visible, with a combo to select an event.

Does that help?
(°v°)

Author

Commented:
Thanks very much for your reply.
Yes I had linked the wrong fields in the query, that's now correct.
I am still having problems with links though.  I can select an event ID from the ContactEvent subform combi box, but I get an error because of the links to the donations and events tables. and if I link them through a query , then I get nothing showing.
  Do the links in the relationships diagram just affect referential integrity, or do they have a further effect?
How can I show a subform so that it also includes details such as event name and donation, which are in separate tables?
Thanks again for your help, I need something to unscramble my mind!!
John.
The relations are used mainly for relational integrity, and their side effects are that, when you create a new query including two tables the relationship is automatically included (but you can delete it), you get a [+] button on the main table's view (which you can disable), and various wizards will pre-fill some information based on existing relationships. This is all meant to help you, nothing is mandatory.

Let's start over (nothing wrong with creating a few new forms, just for the exercise).

Select the table Contacts, and choose (Insert | Autoform). Modify the form for layout, removing most of the fields to make some room. You might already see information from ContactEvent in a subform. It's a subform object, but displaying a table. This is not too smart: you normally don't want the users to open tables directly. Instead, either create a form in datasheet view, or a query showing all fields you need  from the table ContactEvent.

You can now display and edit Contacts, and for each display and edit ContactEvent records. But ID fields are not friendly. You don't want to memorise EventID numbers. If you created a query, open it in design view and modify the properties of the ID field to display a combo box. If you created a form, select the control and change it to a combo box (from the context menu). If you need help setting up combo boxes, ask here.

The result should be that the subform now shows information from the Events table (the name, possibly the date) instead of the bare event number.

You can also bring in more fields from the Events table. Instead of displaying the ContactEvent table alone, display a query linking it to Events. However, this has a disadvantage: if a user changes something, say the date, the actual date of the event will be changed. The user might not be aware of that, so linked fields are often locked.

You can make the same exercise starting with Events. Again, build a main form to manage your events, and display the table ContactEvent as a subform. To edit the ConteventCustID field, display a combo showing for example LastName & ', ' & FirstName.

The first form is meant to manage contacts, and their participation in events, but the events must exist beforehand. The second form manages events, and the list of participants, but it doesn't allow (as it is) to add new Contacts.

I usually showed exactly this type of forms managing many-to-many relationships on the second day of Access introduction classes. It's not entirely trivial, especially if you attempt to learn alone.

I hope this answered some of your questions
(°v°)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks so much for your detailed reply, it has been really helpful
I made a copy of the database so that I could mess around with it, and everything works fine up to making the contacts form with a ContactEvent Subform witha combi box showing the event name, the only problem was that it would not allow me to enter data as it gave an error    "You cannot add or change a record becaus a related record is required in table 'Donations'"     I realised that there is no point in the doantions table, it can all be included in ContactEvents, so that problem went.
When I include the events table in a query with ContactEvent however I get problems as there is no data showing in the query, the combo box disappears and I cannot select anything.
If I can just get over this hurdle, I think the rest will be relatively plain sailing. (I can always hope!)
Thanks again for spending the time to help me.
John.
Since the key of ContactEvent is dual, ConteventCustID & EventID, you need a matching record in both master tables before creating a record. (If the donation ID is required, the same will be true for donations, if if it isn't part of the key.) To link the two tables in a query, use something like:

SELECT CE.*, E.EventName
FROM ContactEvent CE INNER JOIN Events E ON CE.EventID = E.EventID

This will show all fields from CE, and one (or more) fields from the related event. If you change the field CE.EventID, you should see the related field change automatically.

If you don't see any data, it probably means you have set the relationship incorrectly. This would be the case for example if you have linked ContactEvent with Donations using INNER JOIN, but DonationID is Null. If the foreign key can be Null, you need to use LEFT JOIN, so that all records are displayed, even if there is no related record from the joined table.

You can post the query you use here, if you like.

Another possibility is that, when you use a query instead of a table, the subform wizard is no longer able to set the link fields automatically for you. If you are working on the main form Contacts, make sure you have these properties:

    Link Master Fields: ContactID
    Link Child Fields: ConteventCustID

Good luck!
(°v°)

Author

Commented:
Thanks for all your help.
Things are now beginning to work properly.  I am not sure exactly what the last problem was, eventually I deleted the query and re-made it, the result seemed to be exactly the same except that it worked!
The eventual code is below.
A lot more to write yet, but hopefully I can solve the problems without coming back.
John
SELECT ContactEvent.*, Events.EventID AS Events_EventID, Events.EventName, Events.Location, Events.Date, Events.Cost, Events.Comments
FROM Events INNER JOIN ContactEvent ON Events.EventID = ContactEvent.EventID;

Open in new window

Author

Commented:
Very helpful explanations
Thanks, and please don't hesitate to come back when needed!
(°v°)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial