Counting Unique Dates (Access 2K3)

Topic

New Reply

Hello All!

I’m attempting to retrieve the count of unique dates from a table. Some dates have multiple entries, and there are other fields from this same table that I am summing.

Question: how can I write a SQL statement to give me the count of unique dates (which is not necessarily the number of records pulled)? Here is the current SQL:

SELECT tblPavProd.Crew, Sum(tblPavProd.SMSY) AS SumOfSMSY, Sum(tblPavProd.AMSY) AS SumOfAMSY, Sum(tblPavProd.HWSY) AS SumOfHWSY, Sum(tblPavProd.LFCurb) AS SumOfLFCurb, Sum(tblPavProd.BatchedCY) AS SumOfBatchedCY, Count(tblPavProd.Date) AS CountOfDate
FROM tblPavProd
WHERE (((tblPavProd.Date) Between [1st Day] And [Last Day]) AND ((tblPavProd.ConcDepthIn) Is Not Null))
GROUP BY tblPavProd.Crew

There are only 2 crews, so this query returns 2 records. However, it is currently giving a higher count than correct, as it is counting doubled dates twice. (The double dates are needed to distinguish other differences in the entries)

I tried:

Count(SELECT DISTINCT Date FROM tblPavProd) AS CountOfDate

but this produces an error saying ‘There can only be one record returned by this subquery’

Any ideas? Thanks so much in advance.

Reply | Quote

Replies

You cannot return the number of distinct dates at the same time as the sum of several other fields. You need a separate query for that. Create a query that returns distinct dates:

SELECT DISTINCT [Date] FROM tblPavProd WHERE [Date] Between [1st Day] And [Last Day] AND ConcDepthIn Is Not Null

Save it as (say) qryDistinctDates, then create a query that returns the count of dates:

SELECT Count(*) AS CountOfDate FROM qryDistinctDates.

You can combine them in one query:

SELECT Count(*) AS CountOfDate FROM [SELECT DISTINCT [Date] FROM tblPavProd WHERE [Date] Between [1st Day] And [Last Day] AND ConcDepthIn Is Not Null]. AS t

Reply | Quote

Of course…you are the wise Access guru as usual. I actually did get it all in one query, but it’s a doozy:

…Sum(tblPavProd.LFCurb) AS SumOfLFCurb, Sum(tblPavProd.BatchedCY) AS SumOfBatchedCY, (SELECT Count(*) AS CountOfDate FROM (SELECT DISTINCT [Date] FROM tblPavProd WHERE ([Date] Between [1st Day] And [Last Day]) AND (ConcDepthIn Is Not Null) AND ([Crew]=’150′))) AS WorkDays150….

Thanks so much!

Reply | Quote