Query

[Aspen77]

Hello,

I need assistance with a query. I have two tables:

Table 1 - Guest Name
Name of Guest

Table 2- Attendance
Date
Overnight/Dinner Only

I would like to query quests that only attended for Dinner Only for the season and that at no time stayed overnight. So if Jane Smith attended dinner one night and then the next night stayed overnight she would not be counted as a dinner only. Any assistance would be greatly appreciated. Thank you!

 

[dhookom]

Impossible since there are no common fields in the 2 tables. Please provide exact table and field names as well as sample records and expected results from the query.

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Table Guests
ID
Last Name
First Name

Table Guest Attendance
ID
Date
Overnight/Dinner Only

Table Guest
1
Smith
Jane

2
Doe
John

Table Guest Attendance
1
1/1/13
overnight

1
1/2/13
dinner only

2
1/1/13
dinner only

2
1/1/13
dinner only

The only result I would like to see in the query is Guest 2(John Doe). I created a query and it lists all guests that match dinner only however I do not need the guests like Jane Smith that have stayed overnight one night and then had dinner another night. I only want guests that only had dinner throughout the season and did not stay overnight. Hope this makes sense. Thank you.

 

[dhookom]

You can create a query with a subquery in the criteria like:

SELECT Guests.ID, Guests.[Last Name], Guests.[First Name], GuestAttendance.OvernightDinnerOnly
FROM Guests INNER JOIN GuestAttendance ON Guests.ID = GuestAttendance.ID
GROUP BY Guests.ID, Guests.[Last Name], Guests.[First Name], GuestAttendance.OvernightDinnerOnly
HAVING (((Guests.ID) Not In (SELECT ID FROM GuestAttendance WHERE OvernightDinnerOnly ="overnight")) AND ((GuestAttendance.OvernightDinnerOnly)="Dinner Only"));

Note, I might have changed some of your table/field names so I wouldn't have to type so many []s. Consider adopting a naming convention that doesn't allow spaces and special symbols.

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Hello,

I am receiving the following error message:

You tried to execute a query that does not include the specified expression 'And [Guest Attendance].[Overnight/Dinner Only]="Dinner Only" as part of an aggregate function.

Please assist. Thank you!

 

[Aspen77]

Please disregard the previous message,I found the problem. However, when I run the query no data shows up.

 

[Aspen77]

Hello,

Thank you so much for the query, it is partially working. However, the query is returning all guests that ate dinner including the guests that on some evenings stayed overnight. So in the example below I would not want Jane Smith to show because although she ate dinner on 1/1/13 she also stayed overnight on 1/2/13. I would want John Doe to show up because he ate dinner on both evenings. Please advise. Thank you!

Table Guest
Smith
Jane

2
Doe
John

Table Guest Attendance
1
1/1/13
overnight

1
1/2/13
dinner only

2
1/1/13
dinner only

2
1/2/13
dinner only

 

[dhookom]

What is the SQL of the query that is partially working?

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

The SQL code you provided returned all guests that ate dinner to include those guests that also stayed overnight throughout the season, however, I need the results to only include guests that ate dinner only and did not stay overnight throughout the season.

Every night when a guest checks in they are checked either with "Dinner Only" or "Overnight". I am trying to query only those guests that checked in "Dinner Only" throughout the season and at no time checked in as overnight. Thank you!

 

[PHV]

Again, what is YOUR actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aspen77]

SELECT Guests.ID, Guests.[Last Name], Guests.[First Name], [Guest Attendance].[Overnight/Dinner Only]
FROM Guests INNER JOIN [Guest Attendance] ON Guests.ID = [Guest Attendance].ID
GROUP BY Guests.ID, Guests.[Last Name], Guests.[First Name], [Guest Attendance].[Overnight/Dinner Only]
HAVING (((Guests.ID) Not In (SELECT ID FROM [Guest Attendance] WHERE [Overnight/Dinner Only] ="overnight")) AND (([Guest Attendance].[Overnight/Dinner Only])="Dinner Only"));

 

[PHV]

What about this ?
SELECT DISTINCT G.ID, G.[Last Name], G.[First Name], D.[Overnight/Dinner Only]
FROM (Guests G
INNER JOIN [Guest Attendance] D ON G.ID = D.ID)
LEFT JOIN (SELECT ID FROM [Guest Attendance] WHERE [Overnight/Dinner Only] = 'overnight'
) O ON G.ID = O.ID
WHERE D.[Overnight/Dinner Only] = 'Dinner Only' AND O.ID Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aspen77]

Hello,

Thank you for the new code, however, it is still including the guests that have both check-ins (overnight and dinner only). Any assistance would be greatly appreciated. Thank you!

 

[Aspen77]

It's working, thank you so much! You're awesome! Thank you! Thank you! Thank you!