I have a very simple access database (table) with 2 columns ( USERNAME
& DEPARTMENT).  There are about 4 dozen rows.

I would like to create a report/view/etc that will give me a count of
the each distinct departments.  The departments may change in the main
table.

stevenclary-ga

The SQL statement you are looking for is as follows:

SELECT DEPARTMENT, COUNT(DEPARTMENT) AS [DEPARTMENT COUNT]
FROM [table]
GROUP BY DEPARTMENT

You should replace table with the actual table name and DEPARTMENT
COUNT with the preferred column name for the count.

To create a view, use the following script, replacing VIEW NAME with
the desired viewname:

CREATE VIEW dbo.[VIEW NAME]
AS
SELECT DEPARTMENT, COUNT(DEPARTMENT) AS [DEPARTMENT COUNT]
FROM [table]
GROUP BY DEPARTMENT

If you have any questions, feel free to ask for clarification.


Cheers,
tox-ga

Thanks so much!