Query, Sum data by month & year using a date range

Question

Hello, I need to create a report that sums the total by month and year for a date range no matter what day is entered in the start or end dates it still goes by month and year. the sql below im goofing with isnt working what so ever... What am I doing wrong? Thanks!

Example: If I enter a date range 11/3/11  to 1/10/12 the results would be;

11/2011  = the sum for the data for this month

12/2011 = the sum for the data for this month

1/2012 = the sum for the data for this month

[code]

SELECT Sum([DTRegular])/(Sum([EmployeeTime]-[DTReason1Time]-[DTReason2Time]-[DTReason3Time])) AS DelayPercent FROM tblDataEntry WHERE (((Month([dates]![DaysDate])) Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]));

[/code]

Answer 1

That Order By clause is redundant because it's the same as the Group By, which also sorts the records.

As I said before, you really need to use the report's Sorting and Grouping to sort the report.  Even if the report looks like it's sorted correctly without it, some future, seemingly simple change to the report can throw the query's sorting out the window.

You said you added =CDate(monthyear) to the Sorting and Grouping list, but did you put it at the top of the list?  (As far as I can see, that should be the only entry in the list.)

Assuming you get it sorted properly, the only problem is to display the date the way you want.  Since the desired date formating is not in the query, you can do it in the report's date text box.  Set the text box's expression to  =CDate(monthyear) and set its Format property to  mmm-yyyy

Side note:  the use of the format function in the query is just one of several ways to ignore the day of the month part of the date field.  There is no particular significance to using Format instead of any other calculation that generates an equivalent result.

If it would help your understanding of what's going on and maybe make it simpler in the report, you can get the query to use a Date/Time value instead of a formatted text value with any of these:

CDate(Format([DaysDate],"yyyy/mm")) AS MonthYear

DateValue(Format([DaysDate],"yyyy/mm")) AS MonthYear

DateSerial(Year(DaysDate), Month(DaysDate), 1) AS MonthYear

Then the MonthYear field in the report would be a date value and you can format the text box and do Sorting and Grouping on the field without using an expression.

Answer 2

Marshall, I dont understand what your saying. I previously figured out that if I change it to year then month it will sort the way I want both in the query and report but the dates are backward like Year/Month. I tried to format the control and it still displays the same I tried to add the =CDate(monthyear) to the sortings expression and still same thing. Here is my SQL that works except for the reversed date. I would really like it to say Aug-2012 fi possible. Thanks!

[code]SELECT Format([DaysDate],"yyyy/mm") AS MonthYear, Sum([DTRegular])/Sum([EmployeeTime]-[DTReason1Time]-[DTReason2Time]-[DTReason3Time]) AS DelayPercent, Sum([GoodFootage])/Sum([TotalFootage]) AS Recovery

FROM tblDataEntry

WHERE (((tblDataEntry.DaysDate) Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]))

GROUP BY Format([DaysDate],"yyyy/mm")

ORDER BY Format([DaysDate],"yyyy/mm");

[/code]

Answer 3

You need to use the report's Sorting and Grouping feature to specify how the report is sorted.  In your case, set the Field/Expression to:

   =CDate(monthyear)

You can sort the the query the way you want, which may be useful for a form or combo/list box, but just a waste of time for a report:

SELECT Format(DaysDate, "mm/yyyy") As monthyear,

Sum([DTRegular]) / Sum([EmployeeTime]-[DTReason1Time]-[DTReason2Time]-[DTReason3Time]) AS DelayPercent

FROM tblDataEntry

WHERE DaysDate Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]

GROUP BY Format(DaysDate, "yyyymm"), Format(DaysDate, "mm/yyyy")

Answer 4

Marshall, it works great except how can I sort it by year and month? right now it looks like this; I used the date range 1/1/9 - 8/29/12

1-2009 - Sum of data

1-2010 -  Sum of data

1-2011 - Sum of data

1-2012 - Sum of data

2-2009 - Sum of data

2-2010 - Sum of data

ect """""""

I need;

1-2009

2-2009

3-2009

" "

1-2010

2-2010

3-2010

" "

and so on.....

Answer 5

Try this kind of thing:

SELECT Format(DaysDate, "mm/yyyy") As monthyear,

              Sum([DTRegular]) / Sum([EmployeeTime]-[DTReason1Time]-[DTReason2Time]-[DTReason3Time]) AS DelayPercent

FROM tblDataEntry

WHERE DaysDate Between [Forms]![frmSwitchboard].[txtStartDate] And [Forms]![frmSwitchboard].[txtEndDate]

GROUP BY Format(DaysDate, "mm/yyyy")