Inventory Query Help Needed

[txrules]

I am trying to make a chemical inventory database. The main problem I run into is making the query or queries to calculate usage. We do a physical inventory monthly and receive deliveries during the month. I want to get a starting inventory and add all the deliveries, then subtract the ending inventory to get a usage. Then I want to calculate the days between the starting inventory and ending inventory so I can get a daily usage. I would have an inventory table like the following:

TankID InvDate Gallons

Then I would have a delivery table like this:

TankID DelDate Gallons

So I want the last two inventories and all the deliveries between that date range to make the calculation.

I hope I explained it well enough.
Any help would be greatly appreciated.

 

[HiTechCoach]

The best advice I have is to read this:

Inventory Control: Quantity on Hand

 

[txrules]

I tried to make the example to work for me but I just can't seem to get there.

I am not after an On-hand Balance. The physical inventory we do tells me that.

We bill the customer for the chemical when it is delivered. That part is not a problem and does not need to be handled here.

What I need to be able to do is report the quantity used. I was thinking if I could subtract the newest physical inventory from the prior inventory based on the date.
Then I could add the delivery quantities that were made between the two inventory dates. This should give me the total I need.

 

[dcartford]

Let me rearrange your thoughts. Don't split your inventory from your delivery into separate table. Create 1 table to store all your inventory movement, this will allow you to keep track of your individual stock usage and balance at each period. You may need to consider how you can apply the costing mechanism in costing your stock movement (delivery/purchase). Determine your type of costing preference and devise a sproc or trigger to perform this function at the data layer. You may need to consider how to apply stock adjustment, as this will also affect your stock usage and balance.

Good Luck
accountingdes.com

 

[txrules]

I have built several access databases, but I do not write VB. I have edited some VB and made things work for my needs. I get by using multiple queries and canned macros. Things that would make you pros cringe.
I have been working hard to make Allen's example work for me but I just can not get it there.
As for changing my thinking, no problem. I am open to taking a different approach. I am building from the ground up so it can be anyway needed.
As for the task I want to accomplish, it is not a normal inventory database. It needs to work backwards and I need to calculate the daily chemical usage. I do not need to invoice anything. We have over 1000 sites and we must report the daily and monthly usage at each site.
The data we have to work with is a delivery report and a physical inventory. We currently use an Excel sheet with the following columns: StartingInventory, Deliveries, EndingInventory, MonthlyUsage, DailyRate. MonthlyUsage = Starting + Deliveries - Ending. DailyRate= MonthlyUsage/DaysInTheMonth.
The problems are: we have to make a new spreadsheet each month and move the ending numbers to the beginning numbers, deliveries are summed up elsewhere, data doesn't share well, physical inventories have to be made at the end of each month.
I was hoping to fix it by making a database that will fit our needs. I think I have the bulk of it figured out but I could change if needed.
If someone could show me how to get access to make the similar calculation we get in Excel, I think I could get this thing going.