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:
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.
See Question&Answers more detail:os