Group by date for records with date/time field 3

[BrianLe]

In Ac97, I have a table Activity that has a date/time field. There can be multiple records on any day, or no records.

I'm trying to write a query to sum the records for each day so I can generate a report to show the totals for each day of the month, including 0's for days without any records.

Here is some sample data:
WeightDate Unit1 Unit2
10/21/07 6:00 AM 600 200
10/22/07 9:00 PM 300 100
10/23/07 5:00 AM 500 250
10/23/07 7:00 AM 700 800
10/25/07 5:00 AM 200 100

Note the multiple records for 10/23/07 and none for 10/24/07.

I would like the query to return:

WeightDate Unit1 Unit2
10/21/07 600 200
10/22/07 300 100
10/23/07 1200 1050
10/25/07 200 100

Note the sum for 10/23/07.

So that the report will show.

WeightDate Unit1 Unit2
10/21/07 600 200
10/22/07 300 100
10/23/07 1200 1050
10/24/07 0 0
10/25/07 200 100

Note the "0"s for 10/24/07 when there were no records for 10/24/07.

I tried the following query but it doesn't show the WeightDate in the result, it only shows the following.

Unit1 Unit2
600 200
300 100
1200 1050
200 100

SELECT Sum(nz([Unit1],0)) AS Total1, Sum(nz([Unit2],0)) AS Total2
FROM Activity
GROUP BY DateValue(WeightDate);

How do I get the query to include the dates so I can show them in my report?

Thanks,

Brian

 

[LonnieJohnson]

The only thing I can think of is to create a table with all the dates for the month in question then create a one (new table) to many (your table) join. You will also have to change the format of your date field to not include the time for the purpose of this exercise.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[BrianLe]

Lonnie,

The date field includes the time so I can know when (to the minute) the reading was taken for other analysis purposes. With that in mind, can I change the format? If so, how and when should I change the format?

Thanks,

Brian

 

[dhookom]

You can use DateValue(WeightDate) to display and group by only the date part of the field values.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[BrianLe]

I think both of you have me on my way. I created the query Weightometer.

SELECT DateValue([tblActivity].[StartDate]) AS UsageDate, Sum(nz([tblActivity].[WeightometerUnit1],0)) AS Unit1, Sum(nz([tblActivity].[WeightometerUnit2],0)) AS Unit2 
FROM tblActivity
WHERE (((DateValue([tblActivity].[StartDate])) Between [Forms]![frmWeightometerReport]![tbxStartDate] And ([Forms]![frmWeightometerReport]![tbxEndDate])))
GROUP BY DateValue([tblActivity].[StartDate]);

It give me the totals for each day there are records in tblActivity as I wanted.

I also created table ReportDates that will have all the days of the year.

ReportDateID
UsageDate

My problem is combining the results of the query and the table. I've tried the following query but it doesn't return records showing "0's" for Unit1 and Unit2 for dates when there are no records in tblActivity.

SELECT *
FROM tblReportDates INNER JOIN qryWeightometer ON tblReportDates.UsageDate = qryWeightometer.UsageDate
WHERE (((DateValue([qryWeightometer].[UsageDate])) Between [Forms]![frmWeightometerReport]![tbxStartDate] And ([Forms]![frmWeightometerReport]![tbxEndDate])));

Thanks,

Brian

 

[PHV]

Use LEFT JOIN instead of INNER JOIN.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

PH,

I tried the LEFT JOIN and get message "The expression is typed incorrectly or it is too complex..."

Thanks,

Brian

 

[PHV]

PARAMETERS [Forms]![frmWeightometerReport]![tbxStartDate] DateTime, [Forms]![frmWeightometerReport]![tbxEndDate] DateTime;
SELECT *
FROM tblReportDates LEFT JOIN qryWeightometer ON tblReportDates.UsageDate = qryWeightometer.UsageDate
WHERE tblReportDates.UsageDate Between [Forms]![frmWeightometerReport]![tbxStartDate] And [Forms]![frmWeightometerReport]![tbxEndDate];


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Thanks again to all of you.

Here's what I ended up with.

qry Weightometer

SELECT DateValue([tblActivity].[StartDate]) AS UsageDate, Sum(nz([tblActivity].[WeightometerUnit1],0)) AS Unit1, Sum(nz([tblActivity].[WeightometerUnit2],0)) AS Unit2, Sum(nz([tblActivity].[WeightometerUnit3],0)) AS Unit3
FROM tblActivity
WHERE (((DateValue([tblActivity].[StartDate])) Between [Forms]![frmWeightometerReport]![tbxStartDate] And ([Forms]![frmWeightometerReport]![tbxEndDate])))
GROUP BY DateValue([tblActivity].[StartDate]);

and qryWeightometerReport

PARAMETERS [Forms]![frmWeightometerReport]![tbxStartDate] DateTime, [Forms]![frmWeightometerReport]![tbxEndDate] DateTime;
SELECT *
FROM tblReportDates LEFT JOIN qryWeightometer ON tblReportDates.UsageDate = qryWeightometer.UsageDate
WHERE tblReportDates.UsageDate Between [Forms]![frmWeightometerReport]![tbxStartDate] And [Forms]![frmWeightometerReport]![tbxEndDate];

I just have to figure out how to get "O"'s into the report when there are NULLS. I think I'll be able to figure that out.

Stars to all of you.

Thanks,

Brian