Subtotal Query with Date and Time

[Guitarcam87]

Here is what I have:

tblPerformance
[ProdDate],[Press],[Changeover]

3/3/2013 4:14:36 AM Kase 1 2
3/3/2013 4:14:49 AM Kase 2 3
3/4/2013 6:15:23 AM Kase 1 4
3/4/2013 6:16:16 AM Kase 2 3

*Note* I have to use the time because our plant considers a day from 7am - 7am the next day. I am currently using a form that uses this function to calculate the date and automatically populate the field. =DateAdd("h",-7,Now())

Here is what I need:

A Query to return totals for each day

3/3/2013 5
3/4/2013 7

I have tried using totals parameter in the query but it returns all entries I am guessing due to the specific date code that is in the field.

I am open to suggestions for finding a better way. Thank you for your time!

 

[pr2-eugin]

Try this.. I have not tested it.. but should work..

SELECT DateValue(tblPerformance.[ProdDate]) As qryDateField, Count(tblPerformance.[Press]) As CountOfPress
FROM tblPerformance
GROUP BY qryDateField;

 

[Guitarcam87]

Try this.. I have not tested it.. but should work..

SELECT DateValue(tblPerformance.[ProdDate]) As qryDateField, Count(tblPerformance.[Press]) As CountOfPress
FROM tblPerformance
GROUP BY qryDateField;

I got the following error message...

"You tried to execute a query that does not include the specified expression 'DateValue(tblPerformance.[ProdDate])' as part of an aggregate function.

 

[pr2-eugin]

Oops my bad.. Just change it to the following..

SELECT DateValue(tblPerformance.[ProdDate]) As qryDateField, Count(tblPerformance.[ProdDate]) As CountOfField
FROM tblPerformance
GROUP BY DateValue(tblPerformance.[ProdDate]);

 

[Guitarcam87]

Perfect thankyou!!!!!!

 

[pr2-eugin]

Glad to help !

 

[Guitarcam87]

Maybe you can help me with another aspect now...

What would the syntax look like if I wanted to add another field to the mix. Or is this even possible?

 

[pr2-eugin]

You can do, just go into the Query Builder.. Select the fields that you want, it would auto populate the Total to Group By.. If you want you can also add it manually, but also make sure you add it to the Group By part as well..

SELECT DateValue(tblPerformance.[ProdDate]) As qryDateField, Count(tblPerformance.[ProdDate]) As CountOfField, [COLOR=Blue]tblPerformance.anotherField[/COLOR]
FROM tblPerformance
GROUP BY [COLOR=Blue]tblPerformance.anotherField[/COLOR], DateValue(tblPerformance.[ProdDate]);