Calculate Opening Balance

[yeasir01]

Hello, I'm working on ms access database to track fuel tank inventories for a multi-store operation. Typically on a pen & paper, you would have the following column headings. Which is what I replicated on access. I can't seem to figure out how to code the opening balance to give me the desired result. I did read Allen Brown's tutorial but, it does not apply to this situation as stock take will be done daily for local ordinance compliance. I Tried to supply as much information as possible. Any Help will be greatly appreciated.

**QUERY**
OPENING = Calculated field based on closing from previous day where store_id is equal to the record.
GALLONS SOLD = Sum of (fuel sold * ratio)
DELIVERED = User Inputted
ADJUSTMENTS = User Inputted
BOOK VOLUME = Opening - Gallons Sold + Delivered - Adjustment
CLOSE VOLUME = User Inputted
OVER/SHORT = Close - Book

**TABLE STRUCTURE**

GRADES_TABLE
Reg
Mid
Prm

FUEL_SALES_TABLE
Reg Belongs to Store 1
Mid Belongs to Store 1
Prm Belongs to Store 1

WETSTOCK_INV
87 Tank
91 Tank

TANKS_TABLE
87 Tank Belongs to Store 1
91 Tank Belongs to Store 1

RATIO_TABLE
Reg = 100% 87 Tank
Mid = 50% 87 Tank
Mid = 50% 91 Tank
Prm = 100% 91 Tank

 

[A S MANN]

The table structure and your nomenclature is not clear to me. You need to put all value of your calculation fields to one query and than calculate opening balance. you can also do it via VBA.

 

[arnelgp]

Also you need to put a date field so we only calc up to the last closing date before today.

 

[The_Doc_Man]

This is a standard "inventory" problem. To do this, you need a date field for each "transaction" and the transactions will either positive or negative gallons (and fractions thereof). Then you run a summation query that totals all readings. You do not EVER store an actual opening balance.

If you want to know the opening balance on day X (where X is any date in the past), you simply have your query include a WHERE clause to select only dates earlier than the date in question (presuming that all deliveries, sales, and adjustments also have dates for the exact day on which they occurred).

 

[Pat Hartman]

For some kinds of inventory (liquids for example), you store a closing balance and then calculate the amount used by subtracting the opening balance from the closing balance. To find the previous closing balance (which is the current opening balance) you use a query with a sub query. I'm not going to build the actual query. I don't like building this type of complex query using air code. It just causes confusion. The logic is you need to find the Max primary key value for a record of the same product with a date less than today. The Max primary key is used to join to the the record to pick up the closing balance and use it as today's opening balance.

 

[yeasir01]

Thank you all for your quick response! I am a novice and have never needed to use such functions as max, frankly speaking, I wouldn't even know where to place the snippet of code or how to write it. I'm sure there are tutorials out there that show you how to accomplish this, but I have two variables which complicates matters (1.StoreID 2.Date).

Should the code be in the date field of the query or the calculated field? I would like to upload a screenshot of how the relationships are set up, but I'm unable to because I'm a new member. Thanks in advance.