Solved

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

Posted on 2008-10-05
7
1,087 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
0
Comment
Question by:fvitus
  • 4
  • 3
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22646408
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.
0
 

Author Comment

by:fvitus
ID: 22646457
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
0
 

Author Comment

by:fvitus
ID: 22646543
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
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22646891
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.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 22646900
Great!

For your current issue, you can start out with your previous query and add the meetingNumber as a parameter.
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
WHERE Meetings.MeetingNumber = [Enter Meeting Number];

Open in new window

0
 

Author Closing Comment

by:fvitus
ID: 31503242
thx for the quick replies and for giving me the "extra" reply...
points coming your way...
cheers...
Vitus
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22649077
Glad to help!

Cheers.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question