RE: [MS_AccessPros] Query issue

 

Hi Sean

You can do this by including a subquery which counts the number of attendance records for the given member at the given event, and returns the records where the count is zero.

Something like this:

SELECT tblMembers.[Last] & ", " & tblMembers].[First] AS MemberName FROM tblMembers WHERE
(SELECT Count(*) FROM tblEventAttendance WHERE (EventID=[Forms]![frmSelectAttendees].[curEvent]) AND (MemberID=tblMembers.ID))=0
ORDER BY tblMembers.[Last], tblMembers.[First];

By the way, the first part of the WHERE clause in your other query:
                ((tblMembers.ID)=[tblEventAttendance].[MemberID]) AND
is redundant, because this constraint is already implied by the INNER JOIN.

Also, it's much more efficient to ORDER BY the two individual fields ([Last], [First]) as I have above, rather than concatenating the fields with a comma and sorting by the result.

Best wishes,
Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 22 June 2018 11:18
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Query issue

 

 

Good Evening!

 

I have a database with the following elements:

Table tblMembers with information concerning members of the group

Table tblEvent with descriptions and dates of each event

Table tblEventAttendance to join Members.ID and Event.ID elements to indicate members in attendance at an event.

 

I use a form to select the event, with initiates a second form to select members to attend the event.

 

In the second form, I have a ListBox containing to members who attended the selected event. The following SQL statement works to populate the box:

 

SELECT [tblMembers].[Last] & ", " & [tblMembers].[First] AS Expr1
FROM tblMembers INNER JOIN tblEventAttendance ON tblMembers.ID = tblEventAttendance.MemberID
WHERE (((tblMembers.ID)=[tblEventAttendance].[MemberID]) AND ((tblEventAttendance.EventID)=[Forms]![frmSelectAttendees].[curEvent]))
ORDER BY [tblMembers].[Last] & ", " & [tblMembers].[First];

 

I have a second ListBox, which I would like to contain all in the tblMembers table who are NOT in attendance at the event, but I have been unable to manage a query to return them. Essentially, I want to return all the members from the tblMembers table who do NOT have an entry matching the Event.ID entry in the tblEventAttendance table. Does someone have an idea for the query?

 

I do appreciate it.

 

Thanks!

Sean

 

 

__._,_.___

---

Posted by: "Graham Mandeno" <graham@mandeno.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (12)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___