Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1106
  • Last Modified:

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

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
fvitus
Asked:
fvitus
  • 4
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
fvitusAuthor 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
0
 
fvitusAuthor 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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
fvitusAuthor Commented:
thx for the quick replies and for giving me the "extra" reply...
points coming your way...
cheers...
Vitus
0
 
Kevin CrossChief Technology OfficerCommented:
Glad to help!

Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now