By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,224 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,224 IT Pros & Developers. It's quick & easy.

Inventory History Report w/ Balances

P: n/a
Hello,
I am trying to create a report that displays balance impacting
inventory transactions. I can easily pull the current on-hand balance
in one query and the transaction history in another query. However, I
am struggling with putting these 2 queries together for a report that
looks like this:

PartNum TransDate Transaction Qty CurrentBalance PrevBal
ABC 11/01/05 Shipment 5 50 55
ABC 10/31/05 Receipt 20 55 35
ABC 10/28/05 InvAdj -3 35 38
ABC 10/15/05 Receipt 10 38 28
ABC 10/14/05 Shipment 10 28 38
ABC 10/10/05 Receipt 30 38 8
Obviously the PrevBal is the same as the CurrentBalance from the
previous transaction. I want this report to calculate backward into
history because all I have is the current balance and the historical
transactions.

Any help is appreciated.

Thanks,
rxfrenc

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
In a report, you only need the opening balance to be calculated. Then use a
Running Sum to to the rest.

Example.
1. Put a text box in the Report Header section to get the opening balance
for the report. Its Control Source would probably be a DSum() expression
that gets the opening balance, or whatever you use. So properties:
Control Source: =DSum(...
Name: txtOpenBalance
Format: General Number

2. In the Detail section of your report, add the Qty field as a text box a
2nd time. Properites:
Control Source: Qty
Name: txtQtyRS
Running Sum: Over All
Visible: No

3. Set the Control Source of your CurrentBalance text box to:
=[txtOpeningBalance] + [txtQtyRS]

4. Set the Control Source of your PrevBal text box to:
=[txtOpeningBalance] + [txtQtyRS] - [Qty]

If you did have to do this task in a query only, it would be much less
efficient, but you could use a subquery to calculate the previous or
balance. If subqueries are new, see:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<rx*****@qwest.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hello,
I am trying to create a report that displays balance impacting
inventory transactions. I can easily pull the current on-hand balance
in one query and the transaction history in another query. However, I
am struggling with putting these 2 queries together for a report that
looks like this:

PartNum TransDate Transaction Qty CurrentBalance PrevBal
ABC 11/01/05 Shipment 5 50 55
ABC 10/31/05 Receipt 20 55 35
ABC 10/28/05 InvAdj -3 35 38
ABC 10/15/05 Receipt 10 38 28
ABC 10/14/05 Shipment 10 28 38
ABC 10/10/05 Receipt 30 38 8
Obviously the PrevBal is the same as the CurrentBalance from the
previous transaction. I want this report to calculate backward into
history because all I have is the current balance and the historical
transactions.

Any help is appreciated.

Thanks,
rxfrenc

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.