Beginning and Ending Inventory plus activity on one form

[trwitt]

Hello all,

I am trying to create a from that will display the beginning inventory
for an item at the top of the form and the ending inventory at the
bottom. Sandwiched between them, would be the activity details of the
day.

My table structure is:

ItemID
TransactionDate
TransactionType (increase or decrease)
Gallons (we are tracking liquids)

I can write a Totals query that gives me the ending inventory. I then
created a subform that shows the detail but I cannot get the beginning
inventory. If I use the beginning inventory at the top of the form, I
cannot link the next days transactions.

I am sure there is a way but I have been banging my head against the
wall all night.

Thanks,

Tom

 

[stephen]

Tom,
I have something for you:
I assume that the ItemID is in some kind of sequential
order. If not, you will need somekind of order, time
stamp etc. to get the transactions in the order that you
want them to display in your detail form. Next, I would
add a balance field to your table. Now, make the
following three queries:
1. A min query. Group on the date, make ItemId total
first, and balance total first
2. A max query. Group on the date, make Item Id total
last, and balance total last.
3. A min, max query. Put the first two queries in the
window, and link by the transactionDate only. Bring the
fields you want into the query.