Help with Distinct Count Per location

  • Thread starter Thread starter James
  • Start date Start date
J

James

So I have a database, and I'm trying to find out how many users there are
per branch. Sometimes the users name repeats, so I used the distinct
function, and I used the count function to get the total users. Then I want
to show what their location is, so I also selected location.
This does not work together...what am I doing wrong?



SELECT DISTINCT (Count([Group_Summary].[User])) AS TotalUsr,
Group_Summary.[Location]
FROM Group_Summary;
 
If I read your description correctly, I think you need to a SubQuery to do
the distinct selection first before doing the Count.

Try an SQL String like (***Untested***):

SELECT SQ.[Location], Count(SQ.[User]) As CountPerLoc
FROM
(
SELECT DISTINCT GS.[Location], GS.[User]
FROM [Group_Summary] As GS
) As SQ
GROUP BY SQ.[Location];
 
In Access SQL, you either need two queries (which is simpler to understand) or a
subquery in a query to do this.

SELECT DISTINCT USER, Location
FROM YourTable

Save that as Query1

SELECT Location, Count(USER) as CountEm
FROM Query1
GROUP BY Location

If you need a one query solution, post back.
 
Back
Top