distinct query with record count

  • Thread starter Thread starter Starbuck
  • Start date Start date
S

Starbuck

Hi All

I am using a query in Access 2000 which returns a distinct list of all
accounts in a table. I would also like a second coloumn which gives me a
count of all records in each distinct entry (eg how many times account name
FRED is in the table).

The current formula is -
SELECT Distinct Accname FROM Sales;

Thanks in advance for any help
 
If you use the DISTINCT and then want a count you will get one for each
entry. You need to take off the DISTINCT and place a count against any field
to get a count of each Account.

In the query window you can select TOTALS (sideways looking M) on your
toolbar and then set a field or use the same field AccName; one will be a
Group BY the other Count.
 
Hello,

I am taking a database management class, and the last
several meetings have been about queries. I learned
to "count" on Tuesday! But not in sql yet. On the query
grid make sure that you are counting the field that is
the unique identifier (ex. customer number). Also make
sure in aggregate functions to only use the fields
necessary for your purpose.

I hope this helps.
 
Back
Top