Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
303 views
in Technique[技术] by (71.8m points)

Query for Access Table results with no duplicate Records counted

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: enter image description here

Results: enter image description here

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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 [ ].


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...