Calculate Unique Date Value

[Maverick_777]

Hello everyone,
I need to calculate the number of unique dates found within a report so I can calculate the number of stops per day. The date column has:
9/1/2011
9/1/2011
9/1/2011
9/1/2011
9/1/2011
9/1/2011
9/2/2011
9/2/2011
9/2/2011

The results should count that there are 2 unique dates in the section header of this report.

I would appreciate any thoughts or assistance you all might have...

 

[Maverick_777]

Or I was thinking an expression in the query might be able to place a 1 where it finds the first date and everyother time it finds the same date it provides a 0. Then I can put the field in the do a sum on the expression.???

 

[alansidman]

How about an aggregate query that tells you how many times a date occurs.

SELECT Table6.MDate, Count(Table6.MDate) AS CountOfMDate
FROM Table6
GROUP BY Table6.MDate;

Alan

 

[Joe4]

To take Alan's approach one step further, you can get use code similar to his in a subquery of another query to get one record back that returns the count of unique days like you asked, like this:

SELECT x.TotalCount, Count(x.MDate) as UniqueDays
FROM
(SELECT "Total Count:" as TotalCount, Table6.MDate
FROM Table6
GROUP BY Table6.MDate) as x
GROUP BY x.TotalCount;

 

[Maverick_777]

THanks for the quick response guys. Neither one works as I want it to. It does count how many of the dates it finds, but I am hopefully looking to count each day once only.

I'll keep plugging away...

Thanks guys...

 

[Joe4]

THanks for the quick response guys. Neither one works as I want it to. It does count how many of the dates it finds, but I am hopefully looking to count each day once only.

That is exactly what my code does.

If you run it against your data, it returns one record that says:

Total Count:               2

Is that not what you want? If you are not getting that result, then you probably have not applied my code correctly. If you cannot figure it out, please provide your Table and Field names, and I will help you apply the code toy your situation.

 

[Maverick_777]

Thanks Joe4,
It is very likely that it was operator error... : )

My table is called: "tbl_All_Inspection_Results"
and the field is called: "Inspection Date"

However I need the results to populate in a report.

Lets get this working first and then I'll see about getting it into a report...

 

[Joe4]

OK, this should work. Just create a new query, go to SQL View, and copy and paste this code there:

SELECT x.TotalCount, Count(x.[Inspection Date]) as UniqueDays
FROM
(SELECT "Total Count of Unique Days:" as TotalCount, tbl_All_Inspection_Results.[Inspection Date]
FROM tbl_All_Inspection_Results
GROUP BY tbl_All_Inspection_Results.[Inspection Date]) as x
GROUP BY x.TotalCount;

As far as getting it on your Report, you can create a SubReport using this query as your Control Source. Then imbed this SubReport into your Main Report.

 

[Maverick_777]

Thanks Joe4. It did give me the results you expected.

After lunch I will try to do a sub report (my first) so I'll likely be posting again this afternoon. lol

THanks guys

 

[Maverick_777]

OK I have put the sub report into the main report in several different places and it still shows the same result instead of sub totaling based on the section of the form it is in. Any ideas?