Query Problem (Distinct values from 2 rows)

[gesch]

Hello,

I have a report I'm importing into Access where I need to generate a a count of unique reservations rather than just a count of people at the table for each unique table type.

I.e. if I have the following table:

Table | Reservation | Name
Banquet	|	1	|	Larry David
Banquet	|	2	|	Jeff Garlin
Banquet	|	2	|	Suzie Yellsalot
Banquet	|	3	|	Marty Funkhowser
Banquet	|	3	|	Miss Funkhowser
Group1	|	4	|	Jason Alexander
Group1	|	5	|	Elaine Bennis
Group1	|	6	|	Jerry Seinfeld
Group1	|	7	|	Kramer
Group2	|	8	|	Thomas Jefferson
Group2	|	8	|	Martha Jefferson
Group2	|	8	|	Edith Jefferson
Group2	|	9	|	Ronald Reagan

I am trying to construct a query that will return the following:

Table | # of Reservations
Banquet | 3
Group1 | 4
Group2 | 2

I am able to get the count of one or the other using SELECT DISTINCT. Is there a way in a single query to get the count of distinct reservations for each distinct Table?



Any help would be greatly appreciated

[rpeare]

This is going to be a two step process. I duplicated your data and put it in a table named Tbl_Reservations

use this SQL code and save it as Query1

SELECT Tbl_Reservations.Table, Tbl_Reservations.Reservation
FROM Tbl_Reservations
GROUP BY Tbl_Reservations.Table, Tbl_Reservations.Reservation;

use this SQL code and save it as Query2

SELECT Query1.Table, Count(Query1.Reservation) AS TotalReservations
FROM Query1
GROUP BY Query1.Table;

[alansidman]

I was able to achieve your expected results by building two aggregate queries. The second one is the only one you need to run as it is based upon the first.

See attached.

[rpeare]

hah, same solution

[gesch]

Thanks guys, that worked great. Much appreciated

Seems so logical when split into two queries.

[alansidman]

rpeare;
Great minds thinking alike.

Alan

[gap]

I had the same problem ones.
Here is a solution in just one query using select distinct:

SELECT Table1, Count (t.Reservation) AS NumberofR
FROM (SELECT DISTINCT Table1, Reservation FROM Test) As t
GROUP BY Table1;