Date range comparison

[HOMMER]

The query below does just what I want it ot do, but I want to add one more thing.
I want to add total minutes for the same date range but one year prior. If the date range is 1/1/01 to 1/1/02 I want the same data for total minutes dates 1/1/00 to 1/1/01. I am doing a minutes comparison for the year prior with the same dates.


SELECT DISTINCTROW tbl_reasons_bev.reason AS reason, Sum(tbl_dt_bev.timedown) AS [Total Minutes]
FROM tbl_dt_bev LEFT JOIN tbl_reasons_bev ON tbl_dt_bev.reason = tbl_reasons_bev.ID
WHERE (((tbl_dt_bev.date) Between [Forms]![frm_bev_report]![txtStartDate] And [Forms]![frm_bev_report]![txtStopDate]))
GROUP BY tbl_reasons_bev.reason
ORDER BY Sum(tbl_dt_bev.timedown) DESC;

 

[scriverb]

This will require multiple queries. Naming your first query Query1 let's use the following SQL and name it Query2:

SELECT DISTINCTROW tbl_reasons_bev.reason AS reason, Sum(tbl_dt_bev.timedown) AS [Total Minutes]
FROM tbl_dt_bev LEFT JOIN tbl_reasons_bev ON tbl_dt_bev.reason = tbl_reasons_bev.ID
WHERE (((tbl_dt_bev.date) Between DateAdd("yyyy", -1,[Forms]![frm_bev_report]![txtStartDate]) And DateAdd("yyyy", -1, [Forms]![frm_bev_report]![txtStopDate])))
GROUP BY tbl_reasons_bev.reason
ORDER BY Sum(tbl_dt_bev.timedown) DESC;

Now the third query called qryCombineData:

Select Query1.reason, Query1.[Total Minutes] as SelectedYearMinutes, Query2.[Total Minutes] as YearPriorMinutes
From Query1 LEFT JOIN Query2 on Query1.reason = Query2.reason
ORDER BY Query1.[Total Minutes];


Run just qryCombineData. It will run both the Query1 and Query2 and combine the data selecting the records for the second query from a timeperiod one year earlier.

This is pseudo code so please test it out and if there are problems get back with me for adjustments. Bob Scriver