Multiple Sum in one query?

[alicias4]

Hi there,



What I have is a Count query which is based off of a table. In this Query It lists [No of Guests] in one column 1,2,3,4,5 & 6 and in the other column it gives a count of how many times each one occurred.

This is what it looks like:

No of Guests	Count Of Reservations
1	8
2	14
3	3
4	2
5	2
6	1

What I would like to see in ONE query is a count or sum of No of Guests at 1, 2 and then a sum of greater than 2.
I have created one query in addition to this which has given me the sum of greater than 2, I tried to combine this query and that query into one report but it gave me an error that it could not match them.

I tried doing something like this:

SELECT
(SELECT Sum([Count Of Reservations]) AS Families
FROM [Guests Query]
WHERE ((([Guests Query].[No Of Guests])>2))),
(SELECT Sum([Count Of Reservations]) AS Couples
FROM [Guests Query]
WHERE ((([Guests Query].[No Of Guests])=2))),
(SELECT Sum([Count Of Reservations]) AS Business
FROM [Guests Query]
WHERE ((([Guests Query].[No Of Guests])=1)));


But then it gives me an error saying "Query input must contain at least one query or table"
Is there a way I can take that data and have it show:

No of Guests	Count Of Reservations
Business	8
Couples	14
Families	8

Much help would be appreciated.

[recyan]

Till some one comes along, Just check out if below gives some guidelines :

SELECT 
    qryGuestsType.GuestsType, 
    Sum(qryGuestsType.CountOfReservations) AS SumOfCountOfReservations
FROM 
    (
        SELECT 
            IIf([NoOfGuests]=1,"Business",IIf([NoOfGuests]=2,"Couples","Family")) AS GuestsType, 
            qryGuests.CountOfReservations
        FROM 
            qryGuests
    ) AS qryGuestsType
GROUP BY 
     qryGuestsType.GuestsType;

Note : If you can assign the Guest Type in your First (Source) query, perhaps, things can be easier.

Thanks

[alicias4]

Thank you!! That idea worked. I added that bit to the original query, and then I just used a summary query to total based on the Guest Type. Perfect solution!

[recyan]

Glad you got it working.

Thanks