Query for Access Table results with no duplicate Records counted

Question

I am currently trying to query a table based off two fields, one with a date and the other with records to be counted. The results of the query would list the months as numbers (January = 1, Feb = 2, etc) in one column and the other would list the total number of the individual UNIQUE records. The following query I have;

SELECT DISTINCT Month([Date Received]) AS [Month], Count([1 TeamTracking].[Asset #]) AS [CountOfAsset #] FROM [1 TeamTracking] GROUP BY Month([Date Received]);

To test out and make sure I have the right numbers I exported the data to an Excel file and removed duplicates based off of the date and Asset # field. There can be multiple duplicate Asset #'s in the month and there can be the same Asset # in other months. I only want to count the Asset # once per month and not any duplicates in that month. It should be counted again if it is in a different month. For example this is the results I would expect with the query:

1 Team Tracking Table:

Results:

I've tried and just don't get the right numbers. I've tried both in the property field setting the unique values and records to yes and no luck there either. Any assistance would be appreciated. Thank you.

Answer 1

This needs a nested subquery that first consolidates data to unique records. That query then is source for outer query that counts records.

Assuming dates are always first of each month and data is multi-year, consider:

SELECT DateReceived, Count(*) AS Count FROM (
    SELECT DISTINCT DateReceived, IDnum FROM Tracking) 
GROUP BY DateReceived;

If dates are not always first of month and data is multi-year, consider:

SELECT YearMo, Count(*) AS Count FROM (
    SELECT DISTINCT Format(DateReceived, "yyyymm") AS YearMo, IDnum FROM Tracking) 
GROUP BY YearMo;

If data is not multi-year:

SELECT Month, Count(*) AS Count FROM (
    SELECT DISTINCT Month(DateReceived) AS Month, IDnum FROM Tracking) 
GROUP BY Month;

Strongly advise not to use spaces nor punctuation/special characters in naming convention, nor reserved words as names. If you do, must enclose in [ ].