Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1105
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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