Ending inventory calculation

[BrianLe]

In AC97, I'm trying to calculate the ending inventory on my report StackingTubeInventory, but it doesn't know the starting inventory. The form has a Record Source of qryInventoryStackingTube.

The query is:

SELECT tblBeltScales.WeightDate, tblBeltScales.B1Belt, tblBeltScales.B2Belt, tblBeltScales.StackingTubeAdjustment
FROM tblBeltScales
WHERE (((Format(tblBeltScales.WeightDate,'mm/dd/yy'))>=[Forms]![frmStackingTubeInventoryReport]![tbxStartDate] And (Format(tblBeltScales.WeightDate,'mm/dd/yy'))<=[Forms]![frmStackingTubeInventoryReport]![tbxEndDate]));

I have a textbox for Ending Inventory with control source

=NZ([B1Belt],0)-NZ([B2Belt],0)+NZ([StackingTubeAdjustment],0)

that has Running Sum set to "Over Group"

It calculates the Ending Inventory for the "first" day by taking B1Belt-B2Belt+StackingTubeAdjustment. What I need is it to take the Ending Inventory of the previous day, and then add B1Belt-B2Belt+StackingTubeAdjustment, but it doesn't know the "Ending Inventory of the previous day".

How do I get the Ending Inventory of the previous day into the calculation?

Do I need a field in my tblBeltScales to store the Ending Inventory?

Note: In tblBeltScales there is not necessarily a record for each day, and there may be multiple records for each day.

Thanks,

Brian

 

[lameid]

Perhaps you don't want the Running Sum "Over Group" but "Over All".

 

[BrianLe]

Thanks lameid, but I may not have been clear what my problem is.

Let's say I run the report for the period 10/23/07 to 10/25/07. Assume the ending inventory on 10/22/07 was 200, so the beginning inventory on 10/23/07 is also 200.

The values on 10/23/07 are:

B1Belt = 100
B2Belt = 50
StackingTubeAdjustment = 25

Therefore, the ending inventory on 10/23/07 should be:

200 + 100 - 50 + 25 = 275.

My problem is that the query/report doesn't know that the beginning inventory on 10/23/07 was 200 because I didn't query for 10/22/07. I get an ending inventory on 10/23/07 of:

100 - 50 + 25 = 75.

because it doesn't know the inventory from before the selected dates.

How do I get it to know the previous ending inventory?

Maybe I have to run one query from when the beginning inventory was "0", and then query again for the dates I want to show on the report.

Thanks,

Brian

 

[lameid]

I would add the beginning inventory to the underlying query.

If running for a date, it should be relatively easy to make a query that calculates the beginning inventory and add it to your existing recordsource.

An alternative would be to use the Dsum function as the control source in a text box to get the previous balance. This will be slower but still an option. It is easier to implement for multiple days but a query solution for multiple days is not out of reach either.