Previous balance into report

[BrianLe]

I accidentally started a similar thread on the Forms forum.

In AC97, I need help getting a previous balance into a report.

I have a table tblBeltScales, with sample data as follows:

WeightDate B1Belt B2Belt StackingTubeAdjustment
10/21/07 600 200 0
10/22/07 300 100 50
10/23/07 500 250 0
10/23/07 700 800 0

In the report I want to show the following when the report is run from 10/22/07 to 10/23/07 (assume there was no inventory before 10/21/07).

Beginning Stacking Tube Ending
Date Inventory B1Belt B2Belt Adjustments Inventory
10/22/07 400 300 100 0 600
10/23/07 600 500 250 50 900
10/23/07 900 700 800 0 800

Where Ending Inventory = Beginning Inventory + B1Belt - B2Belt + StackingTubeAdjustment

I'm using form frmStackingTubeInventoryReport to select the start and end dates for the report. It has a toggle button with event procedure

I'm using the following query qryStackingTubeInventory to get the B1Belt, B2Belt, and Adjustment values for the report.

SELECT tblBeltScales.B1Belt, tblBeltScales.B2Belt, tblBeltScales.StackingTubeAdjustment
FROM tblBeltScales
WHERE DateValue([tblBeltScales].[WeightDate]) Between
   [Forms]![frmStackingTubeInventoryReport]![tbxStartDate] And
   ([Forms]![frmStackingTubeInventoryReport]![tbxEndDate]);

The above will get the B1Belt, B2Belt, and StackingTubeAdjustment, and the Ending Inventory is calculated in the report OK.

I have the following query named "tblBeltScales Query" that will get me the beginning inventory.

SELECT Sum(nz([B1Belt],0)-nz([StackingTubeAdjustment],0)-nz([B2Belt],0)) AS Balance
FROM tblBeltScales
WHERE DateValue([tblBeltScales].[WeightDate]) < ([Forms]![frmStackingTubeInventoryReport]![tbxEndDate]);

However, I can't figure out how to get the "Balance" into the report for the "Beginning Inventory", particularly for the 1st day shown on the report.

I've tried a text box tbxBalance in the report for the beginning inventory with a control source

=[tblBeltScales Query]![Balance]

but I get the message "Enter Parameter Value tblBeltScales Query"

Do I need to combine the queries into one somehow so I can select Balance, WeightDate, B1Belt, B2Belt, and StackingTubeAdjustment all from the same query for the control source of tbxBalance, tbxB1Belt, tbxB2Belt, tbxStackingTubeAdjustment, and tbxWeightDate?

Any suggestions?

Thanks,

Brian

 

[BrianLe]

Should I post this in the "Reports" forum? I've been working on this all weekend, and am still stuck.

Thanks,

Brian

 

[HarleyQuinn]

I'd agree with your idea at the end of your post and just add the Balance into the query. Just use the exact same expression you've used in your balance query and put it into qryStackingTubeInventory. Something like this:

SELECT tblBeltScales.B1Belt, tblBeltScales.B2Belt, tblBeltScales.StackingTubeAdjustment, Sum(nz([B1Belt],0)-nz([StackingTubeAdjustment],0)-nz([B2Belt],0)) AS Balance
FROM tblBeltScales
WHERE DateValue([tblBeltScales].[WeightDate]) Between
   [Forms]![frmStackingTubeInventoryReport]![tbxStartDate] And
   ([Forms]![frmStackingTubeInventoryReport]![tbxEndDate])
GROUP BY tblBeltScales.B1Belt, tblBeltScales.B2Belt, tblBeltScales.StackingTubeAdjustment;

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[BrianLe]

Thanks Harley.

I must have been half out of my mind, or asleep, when I made my post.

My desired result would be:

Beginning Stacking Tube Ending
Date Inventory B1Belt B2Belt Adjustments Inventory
10/22/07 400 300 100 50 550
10/23/07 550 500 250 0 800
10/23/07 800 700 800 0 700

Based on the corrected equation

Ending Inventory = Beginning Inventory + B1Belt - B2Belt - StackingTubeAdjustment

I tried your suggestion (also including WeightDate in the query), which is now:

SELECT tblBeltScales.WeightDate, tblBeltScales.B1Belt, tblBeltScales.B2Belt, tblBeltScales.StackingTubeAdjustment, Sum(nz([B1Belt],0)-nz([StackingTubeAdjustment],0)-nz([B2Belt],0)) AS Balance
FROM tblBeltScales
WHERE DateValue([tblBeltScales].[WeightDate]) Between
   [Forms]![frmStackingTubeInventoryReport]![tbxStartDate] And
   ([Forms]![frmStackingTubeInventoryReport]![tbxEndDate])
GROUP BY tblBeltScales.WeightDate, tblBeltScales.B1Belt, tblBeltScales.B2Belt, tblBeltScales.StackingTubeAdjustment;

and get the following:

Beginning Stacking Tube Ending
Date Inventory B1Belt B2Belt Adjustments Inventory
10/22/07 150 300 100 50 300
10/23/07 -100 700 800 0 -200
10/23/07 250 500 250 0 500

WeightDate also has a time associated with it. I'm grouping the report on WeightDate. There is also a BeltScalesID (autonum) in the BeltScales table if that could help.

The Beginning Inventory (Balance) is being calculated from the selected days values, not previous days values (i.e. 10/22/07 Beginning Inventory = 300 - 100 - 50 = 150). That is why I had used in the query for the balance a date less than the first day selected (note the change from EndDate to StartDate in query - asleep again).

WHERE DateValue([tblBeltScales].[WeightDate]) < ([Forms]![frmStackingTubeInventoryReport]![tbxStartDate]);

The text box for Ending Inventory in my report has the following control source (no running sum)

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

I tried using a subreport (tblBeltScalesQuery)to show the balance using my original query. The Balance is correct in the subreport.

Then I added a text box to the main report with control source:

=[Reports]![tblBeltScalesQuery]![tbxBalance]

This doesn't work. It gives me "#Name?".

Any more suggestions?

Thanks,

Brian

 

[BrianLe]

I think I'm making some more progress.

To try and understand how sub reports work, I figured I would start working with displaying a single day on a report.

If I use a new form frmBeltScalesQuery to select a start date, and use the command button

Private Sub Toggle10_Click()
DoCmd.OpenReport "tblBeltScalesQuery", acViewPreview
End Sub

to open the subform it has the correct balance.

If I then minimize it, and then open the main report using frmStackingTubeInventoryReport to select the start date for the main form, the balance from the subform appears in the textbox tbxBeginInventory and it works correctly.

How do I get the sub report to open and then the main report by selecting a single start date from a single report?

Thanks,

Brian