Join query question 2

[Rene1024]

Hi,
I have the following query:
(SELECT qryRegistrantEvents.[Confirmation#], qryRegistrantEvents.LastName, qryRegistrantEvents.FirstName, qryRegistrantEvents.Title, qryRegistrantEvents.Organization, qryRegistrantEvents.Affiliation, qryRegistrantEvents.Reg, qryRegistrantEvents.EventId, qryRegistrantEvents.EventName, True AS ConferenceRegistrant, Sum(tblRegEvents.Qty) AS Guests
FROM tblRegEvents RIGHT JOIN qryRegistrantEvents ON (tblRegEvents.EventId = qryRegistrantEvents.EventId) AND (tblRegEvents.[Confirmation#] = qryRegistrantEvents.[Confirmation#])
GROUP BY qryRegistrantEvents.[Confirmation#], qryRegistrantEvents.LastName, qryRegistrantEvents.FirstName, qryRegistrantEvents.Title, qryRegistrantEvents.Organization, qryRegistrantEvents.Affiliation, qryRegistrantEvents.Reg, qryRegistrantEvents.EventId, qryRegistrantEvents.EventName, True)
UNION (SELECT Registration.[Confirmation#], Registration.LastName, Registration.FirstName, Registration.Title, Registration.Organization, Registration.Affiliation, Registration.Reg, tblRegEvents.EventId, tblEvents.EventName, False AS ConferenceRegistrant, Sum(tblRegEvents.Qty) AS Guests
FROM (Registration INNER JOIN tblRegEvents ON Registration.[Confirmation#] = tblRegEvents.[Confirmation#]) INNER JOIN tblEvents ON tblRegEvents.EventId = tblEvents.EventId
GROUP BY Registration.[Confirmation#], Registration.LastName, Registration.FirstName, Registration.Title, Registration.Organization, Registration.Affiliation, Registration.Reg, tblRegEvents.EventId, tblEvents.EventName
HAVING (((Registration.Reg) Is Null Or (Registration.Reg)="")));

It returns all the participants weather they have a guest or not. I would only like to see the ones that have gusts.

Where do i need to insert my "where Guests is equal or greater than 1" statement to accomplish it?

Thanks for any help you can provide.

Regards,

Rene.

 

[PHV]

HAVING Trim(Registration.Reg & "")="" AND Sum(tblRegEvents.Qty)>1

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

 

[Rene1024]

Thanks,

I replaced the HAVING line, but now i get a syntax error in union query message.

what am i doing wrong?

 

[PHV]

What about this ?
SELECT R.[Confirmation#], R.LastName, R.FirstName, R.Title, R.Organization, R.Affiliation, R.Reg, R.EventId, R.EventName, True AS ConferenceRegistrant, Sum(E.Qty) AS Guests
FROM qryRegistrantEvents R INNER JOIN tblRegEvents E ON R.EventId=E.EventId AND R.[Confirmation#]=E.[Confirmation#]
GROUP BY R.[Confirmation#], R.LastName, R.FirstName, R.Title, R.Organization, R.Affiliation, R.Reg, R.EventId, R.EventName
HAVING Sum(E.Qty)>=1
UNION SELECT R.[Confirmation#], R.LastName, R.FirstName, R.Title, R.Organization, R.Affiliation, R.Reg, E.EventId, V.EventName, False, Sum(E.Qty)
FROM (Registration R
INNER JOIN tblRegEvents E ON R.[Confirmation#]=E.[Confirmation#])
INNER JOIN tblEvents V ON E.EventId=V.EventId
GROUP BY R.[Confirmation#], R.LastName, R.FirstName, R.Title, R.Organization, R.Affiliation, R.Reg, E.EventId, V.EventName
HAVING Trim(R.Reg & "")="" AND Sum(E.Qty)>=1

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

 

[Rene1024]

Perfect!

Thanks you so much!