distinct query with record count

[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

 

[GVaught]

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.

 

[ninjavicki]

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.

 

[Van T. Dinh]

Try the SQL String

SELECT AccName, Count(SalesID)
FROM Sales
GROUP BY AccName

 

[Starbuck]

Thanks for the tips fellas

Try the SQL String

SELECT AccName, Count(SalesID)
FROM Sales
GROUP BY AccName