How can I count unique accounts?

[chris-evans]

Hi I have an access table with 5 fields:

1) Month of Joining
2) User ID
3) Date of transaction
4) Value of transaction
5) Country

I am wanting to count the number of unique user ID's in each month of joining for a specific country, e.g. the UK. Because there is a new row for every transaction, I can't just count the User ID's for each month as the query will duplicate the entries. I was thinking that the DCount query would work but i can't get it to.

My query was had three fields:

a) Month of Joining (grouped by)
b) Unique Users: DCount("[User ID]","My Table Name")
c) Country (with criteria = "uk")

This doesn't appear to work, could someone help me out where i am going wrong?

Many thanks.

Chris

 

[Joe4]

One way is to do this in a series of two queries.

First Query
- Add your three fields (Month of Joining, User ID, and Country)
- Add the country criteria ("uk")
- Click on the Totals button to make this an Aggregate Query. This will add a Totals Row with the phrase "Group By" under each field

You will now have a query with all duplicates weeded out.

Now, create a second query based on your first query

Second Query
- Add all three fields from Query 1
- Click on the Totals button to make this an Aggregate Query. This will add a Totals Row with the phrase "Group By" under each field
- Under the User ID field, change the Totals Row from "Group By" to "Count"
- View the results

This should give you what you are looking for.

 

[chris-evans]

That's great thanks. I was just wondering if there was a way to do it in one, but i guess not!

 

[xenou]

Create a query to select DISTINCT, then query that query for the totals:

SELECT 
	TSub.UserID, 
	TSub.MonthJoined, 
	TSub.Country, 
	COUNT(TSub.UserID) AS TotalByCountryByMonth
FROM
	(
	SELECT DISTINCT
		UserID, 
		DateSerial(Year(JoinDate), Month(JoinDate)+1, 0) AS MonthJoined, 
		Country FROM Table2
	) TSub
GROUP BY 
	TSub.UserID, 
	TSub.MonthJoined, 
	TSub.Country