Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting Unique Dates (Access 2K3)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting Unique Dates (Access 2K3)

    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

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting Unique Dates (Access 2K3)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •