We help IT Professionals succeed at work.

Query of 2 tables with "intersection" table that uses "lookup"

fvitus
fvitus asked
on
1,115 Views
Last Modified: 2013-11-29
Objective: Store and retrieve members attendance to meetings.
Tables
Members
   ID
   Firstname
   Lastname
Meetings
   ID
   MeetingNumber
Attendance
   ID
   MembersID [lookup into "Members" for easy selection and storage of attendance]
   MeetingsID [lookup into "Meetings" for easy selection and storage of attendance]

Relationships
ID in Members to MembersID in Attendance [one to many // join type: inner]
ID in Meetings to MeetingsID in Attendance [one to many // join type: inner]

Creating new records in "Attendance" works fine due to the convenience of the lookup display control - All good so far.

Now to the query that will give me the list of who attended what meeting (later I need to input the meeting I want to see - but for now first step is to get the full list to work...)

Query (it got generated by the query designer in Access 2007):
SELECT Members.FirstName, Members.SurName, Meetings.MeetingNumber
FROM Meetings INNER JOIN (Members INNER JOIN Attendance ON Members.ID = Attendance.MembersID) ON Meetings.ID = Attendance.MeetingsID;

This WORKS - but only when:
The Attendance fields MemberID and MeetingsID are NOT lookups (in "Display Control")

The moment I make the "lookup" the query stops working... However I'd like it to still work in this case - Help?!

cheers...
Vitus
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
How do you have the lookup configured.  This should work as long as you have the lookup storing the ID from the respective tables.  You can show whatever you want for lookup purposes, but it must be the ID that is in the raw data.

Author

Commented:
Hi mwvisa,

I use 100% standard settings that come up when you switch from "Text box" to "List box"
I have previously experimented with what column to show - so I am good on that but I can't find anywhere to set/change/view/correct the "what to store" parameter that you mention.

Does this help you to help me? ;-)

cheers...
Vitus

Author

Commented:
Hi again,

so - I deleted all my tables completely and started over.
And now it works... so... obviously I must have click on something earlier on that then made it not work...

Ok - for me now it is to make it so I can input MeetingNumber and get a list of the Attendees for that meeting... any suggestions? I aim to run the query; provide the MeetingNumber; get the results and export to HTML (and put it on the web for the club)...

cheers...
Vitus
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
In the design view for the table, when you click on the column that is your lookup there should be a field in the lookup section at the bottom that says "Bound Column".  Ensure that is set to appropriate column for your ID and that your ID is in the query listed as "Row Source" for your combo box.
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
thx for the quick replies and for giving me the "extra" reply...
points coming your way...
cheers...
Vitus
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Glad to help!

Cheers.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.