Finding previous total for report 1

[BrianLe]

I'm still trying to solve my problem with getting a previous total/balance/inventory into a table so I can use it in a report. I have a table tblActivity

tblActivity
ActivityID
StartDate - format General Date
WeightometerUnit1- number
WeightometerUnit2- number

Typical Data
1 11/21/07 200 400
2 11/22/07 300 200
3 11/22/07 500 300
4 11/23/07 800 200
5 11/25/07 600 500

I would like to be able to run a report for say 11/23-11/25 that would show the following:

Usage Previous Current
Date Total Unit1 Unit2 Total
11/23/07 1900 800 200 2900
11/24/07 2900 0 0 2900
11/225/07 2900 600 500 4000

I have query qryDailyUnitTotals

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

If I select 11/23/07 - 11/25/07, the query gives me

UsageDate Unit1 Unit2 Total
11/23/07 800 200 1000

If I select 11/2/07 - 11/26/07, the query gives me

UsageDate Unit1 Unit2 Total
11/23/07 800 200 1000
11/25/07 600 500 1100

I have query qryPreviousTotal

SELECT Sum(nz(WeightometerUnit1,0)+nz(WeightometerUnit2,0)) AS PreviousTotal
FROM tblActivity
WHERE (((DateValue(tblActivity.StartDate))<Forms!frmWeightometerReport!tbxStartDate));

It will return one total amount for all the days prior to the selected start date.

So if I select 11/23/07, it returns "1900", which is what I want as my "Previous Total" for 11/23/07 in my report.

What I can't do is use that "Previous Total" in my report for the "Previous Total" so I can then add the daily amounts to it to get the "Current Total".

If I put a Running Sum for the Total in the report, it only sums the values for the selected dates.

Any suggestions?

Thanks,

Brian

 

[BrianLe]

OOPs. The dates for

"If I select 11/2/07 - 11/26/07, the query gives me"
below the first query should be 11/23/07- 11/26/07

 

[GingerR]

Ok, I did this, tested it and got this answer:

CalendarDate	Previous Unit1	Unit2	Curr
11/23/2007	1900	800	200	2900
11/24/2007	2900	0	0	2900
11/25/2007	2900	600	500	4000
11/26/2007	4000	0	0	4000

You need a table called tblCalendarDates, with every date listed in it. That way you can get the in-between dates that have no data. So make a table called tblCalendarDates and fill it with every date that you'll ever need (like from 1/1/2000 - 12/31/2020 or something; i can email you mine if you want just let me know; or you may want to build it in Excel (using drop-down it's easier) then copy/paste into an access table).

FIRST QUERY (called qryPreviousTotals): uses tblCalendarDates and builds running sums of the data between the dates in the form:

SELECT tblCalendarDays.CalendarDate, IIf([CalendarDate]=DMin("StartDate","tblActivity"),0,CLng(DSum("WeightometerUnit1","tblActivity","StartDate<#" & [CalendarDate] & "#"))) AS Unit1RollingSum, IIf([CalendarDate]=DMin("StartDate","tblActivity"),0,CLng(DSum("WeightometerUnit2","tblActivity","StartDate<#" & [CalendarDate] & "#"))) AS Unit2RollingSum, [Unit1RollingSum]+[Unit2RollingSum] AS PreviousTotal
FROM tblCalendarDays LEFT JOIN tblActivity ON tblCalendarDays.CalendarDate = tblActivity.StartDate
WHERE (((tblCalendarDays.CalendarDate) Between [Forms]![frmWeightometerReport]![tbxStartDate] And ([Forms]![frmWeightometerReport]![tbxEndDate])));

SECOND QUERY (uses query #1, gets current totals and brings in the "Previous Totals" and adds them for Current Total):

SELECT qryPreviousTotals.CalendarDate, qryPreviousTotals.PreviousTotal, Sum(nz(tblActivity.WeightometerUnit1,0)) AS Unit1, Sum(nz(tblActivity.WeightometerUnit2,0)) AS Unit2, Sum([PreviousTotal]+nz([WeightometerUnit1],0)+nz([WeightometerUnit2],0)) AS CurrentTotal
FROM tblActivity RIGHT JOIN qryPreviousTotals ON tblActivity.StartDate = qryPreviousTotals.CalendarDate
GROUP BY qryPreviousTotals.CalendarDate, qryPreviousTotals.PreviousTotal;

Hope this helps! Let us know how it goes.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BrianLe]

Ginger,

Your queries do the trick.

Using a table with a field for the actual physically counted (not calculated) inventory "StockTake" value is suggested by others so you don't have to sum from the first record each time. You only have to sum from the last "StockTakeDate".

Until I see a real performance issue, which I don't expect to see, I'll be using your suggestion.

Many thanks & Happy Holidays,

Brian