Problems adjusting Inventory Levels

[dragongunner0351]

Hello all, I have a bit of a problem that seems to be snow balling on me. I'm not sure if I should go back to design to resolve this but here is my problem:

I have a table that holds all product information Product Name, Description, Quantity, etc...

The two controls that are critical to what I am trying to accomplish is:

BeginningBalance
and
QuantityAvailable

Beginning Balance is the Number of Parts when inventory was first taken, (right now the inventory controller has the ability to type in this value directly) and is therefore the base number for all future calculations.

QuantityAvailable is adjusted based off of parts orders it has the following source:
=[Order Details Subform].[Form]![BeginningBalance]-[Order Details Subform].[Form]![Qty Shipped]

This works great only if a particular product is ordered only once. However that is not going to happen.

What I need to happen is as parts are ordered the BeginningBalance Control needs to constantly update with the actual value on hand (I guess off of the current value of the QuantityAvailable Control) as orders are processed.

Any help will be enormously appreciated.

 

[lespaul]

you have discovered one of the reasons that storing calculated values isn't a good design decision. Have you looked at the Northwind database inventory tables? That would give you a good idea of where to start.

Additionally, reading the fundamentals document below will help your future design processes.

Basically you would have a "Beginning Balance" entry and then each transaction listed, purchases & returns have a positive quantity, sales and other deductions have a negative quantity. You then sum all the transactions to get your quantity on hand.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dragongunner0351]

Thank you for the reply I will follow your advise and look at the Northwind sample. Although the Northwind Sample I have doesn't show inventory levels being adjusted, do you know of a link to maybe a more up to date version? I am using Office 2003 but the sample looks just like the Northwind sample that came with Office 97

 

[lespaul]

Like I said, you don't adjust the inventory level, you calculate the quantity on hand by summing the quantity field:

ItemID Qty TransactionType
1 600 BeginBalance
1 100 Purchase
1 -250 Sale

query:

SELECT ItemID, SUM(Qty) As QuantityOnHand FROM TableName GROUP BY ItemID

results:
ItemID QuantityOnHand
1 450


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dragongunner0351]

The query you are showing do I put that in the Control Source for my QuantityOnHand Control?

 

[lespaul]

That's a basic query to show the QuantityOnHand if the table is structured with additions to inventory being a positive transaction and subtractions to inventory being a negative transaction.

If your table had these entries:[tt]
ItemID Qty TransactionType
1 600 BeginBalance
2 400 BeginBalance
3 1000 BeginBalance
2 100 Purchase
1 -250 Sale[/tt]

then the above query would return:
[tt]ItemID QuantityOnHand
1 350 (BeginBalance of 600 - Sale of 250)
2 500 (BeginBalance of 400 + Purchase of 100)
3 1000 (BeginBalance of 1000 with no transactions)[/tt]

I don't know what your "QuantityOnHand" control is or if you need this query as its source.





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi