Solved

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

Posted on 2008-10-05
7
1,085 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Glad to help!

Cheers.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now