CODEWITHSUNDEEP
ms-accessunique
CodeyMcCode
CodeyMcCode

Reputation: 21

Count Unique Dates in Relation to Another Field

I have a member identifier reference number in one field and a date in another field.

The database contains rows where the member identifier appears more than once. For a given member, who can have multiple records (rows), sometimes the date is the same and sometimes it is different.

I want to count the number of unique instances of date against the member.

Upvotes: 1

Views: 1921

Answers (2)

p0lar_bear
p0lar_bear

Reputation: 2265

What you want is a GROUP BY clause with the aggregate function COUNT... but on a subquery that uses SELECT DISTINCT to get unique User ID/Date combinations first.

With this record set:

Record set

You can either use the Query Designer to make two queries, the first one being:

query designer for "DistinctDates" query

And the second, which uses the first query you created, being:

query designer for "CountQuery" query

To get the output you want:

query results

Alternatively, if you're like me and prefer to work with raw SQL, you can just use this SQL in a single query:

SELECT DistinctDates.UserID, Count(DistinctDates.TheDate) AS NumDates
FROM (SELECT DISTINCT Table1.UserID, Table1.TheDate FROM Table1) AS DistinctDates
GROUP BY DistinctDates.UserID;

One thing extra that I'd like to note, which I learned just now off of W3Schools's SQL SELECT DISTINCT Statement page, is that most other RDBMS engines may allow you to use DISTINCT on fields in a query. However, The MSAccess/Jet Engine doesn't support that syntax. If you're in another DB engine at any point, you could just do this:

SELECT Table1.UserID, COUNT(DISTINCT Table1.TheDate) AS NumDates FROM Table1;

Upvotes: 1

Gustav
Gustav

Reputation: 55806

You can use:

Select MemberID, Count(*) As DateCount
From (Select Distinct MemberID, DateField From YourTable)
Group By MemberID

Upvotes: 2

Related Questions