Calculations (1 Viewer)

[MelissaMuz]

Hello!

I am making a stock and orders database and I need some help with calculating the totals.

I have a table called Orders, as well as a Parts table (among others). I have the Quantity On Hand of each part listed in the Parts table.

When we receive an order (indicated with a tick box on the Orders table), I would like the Parts table to have the quantity updated for the corresponding part.

 

[The_Doc_Man]

That isn't the way this is normally done. Normally, you would have a transaction table where you have parts ordered, parts sold, parts adjusted due to shrinkage, parts adjusted due to return of purchase, etc. Then your inventory is simply a SUM of transactions grouped by part number.

Doing it your way, you would have to trap the order and generate an UPDATE query for your QOH value for the part.

The way you asked the question, though, makes me wonder. Are you directly updating your orders table from your screen, or is there a form in front of all of this? Because direct updates to any table rarely (99.99+% NEVER) will cause any other actions.

I'm going to suggest that you search the forum for "Inventory" - but here is a link to Allen Browne's thoughts on inventory control.

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

Read the last paragraph of his article carefully because it shows you the perils of using a Quantity On Hand style. QOH inventories require FAR more work than a transaction-based inventory.

 

[bob fitz]

Hello!

I am making a stock and orders database and I need some help with calculating the totals.

I have a table called Orders, as well as a Parts table (among others). I have the Quantity On Hand of each part listed in the Parts table.

When we receive an order (indicated with a tick box on the Orders table), I would like the Parts table to have the quantity updated for the corresponding part.

You may find this of interest: http://allenbrowne.com/AppInventory.html

 

[ebs17]

I have the Quantity On Hand of each part listed in the Parts table.

This is wrong structure. It is better not to save stocks, but to calculate them in a query from saved movements (inflows, outflows).

With a saved movement, you know who is doing it, what it is for, and when it happens. You can look up stocks at any point in time.

 

[MelissaMuz]

Thank you all, I have been toying with the idea of making a separate table for the Quantities but I hadn't needed it up until this point.

Allen Browne - Inventory Control: Quantity on Hand​

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value. allenbrowne.com

I am certainly going to look into this.

 

[MelissaMuz]

The way you asked the question, though, makes me wonder. Are you directly updating your orders table from your screen, or is there a form in front of all of this? Because direct updates to any table rarely (99.99+% NEVER) will cause any other actions.

I am not sure what is meant by this, but I have a form for inputting new orders if that is the question.

 

[The_Doc_Man]

OK, good - if you have a form then you have the most important part of the framework you need. To update another table based on an order table, you need some kind of event because Access is an event-driven framework. If you have to do an update "behind the scenes" then you could do it in event code related to a form's controls. Table-based events tend to be limited, but form-based events are more powerful and flexible. Which is why I asked the question the way I did.

If you have not done so yet, read that last part of the Allen Browne article. He describes situations where you would have to update your QOH table which will require you to run an update. That update has to be triggered by/in event code based on data that is best presented via a form. Have you done any event-based code yet? Because if you haven't, you are about to.

 

[MelissaMuz]

OK, good - if you have a form then you have the most important part of the framework you need. To update another table based on an order table, you need some kind of event because Access is an event-driven framework. If you have to do an update "behind the scenes" then you could do it in event code related to a form's controls. Table-based events tend to be limited, but form-based events are more powerful and flexible. Which is why I asked the question the way I did.

If you have not done so yet, read that last part of the Allen Browne article. He describes situations where you would have to update your QOH table which will require you to run an update. That update has to be triggered by/in event code based on data that is best presented via a form. Have you done any event-based code yet? Because if you haven't, you are about to.

I have read the Allen Browne article, yes and I understand it will be much harder this way, so I guess I will have to make some changes.

Have you done any event-based code yet? Because if you haven't, you are about to.

I have done a very small amount! But not much at all.

 

[MelissaMuz]

Normally, you would have a transaction table where you have parts ordered, parts sold, parts adjusted due to shrinkage, parts adjusted due to return of purchase, etc. Then your inventory is simply a SUM of transactions grouped by part number.

I'm not too sure how this would work on my db, because it is I suppose a 'project' that is the 'order', the parts are what goes into the project, i want to store what parts each project needs and track when they have been ordered and received as well as parts we already have in stock for future projects so we know whether or not to order more! I'm not sure what 'parts adjusted due to shrinkage' means but we do not have returns.

 

[CJ_London]

I'm not sure what 'parts adjusted due to shrinkage'

your system says you have 100 of an item in stock. A physical stock check is done and only 98 are counted - so the shrinkage is 2. Personally I use the phrase 'stock check adjustment' because when you count you might find you have 103. Doesn't sound like it would apply in your case but the other 'shrinkage' could be due to stock going out of date.

Either way I agree with Doc - one table for transactions with a field to indicate type of transaction. You can even include the project required quantities as a transaction - just modify your query to calculate current stock to ignore them. Assuming you include a project ID field you can then easily determine what stock has been issued to what project and how much is still required.

If most of your data is manually entered, I just store a positive number (easier for the user) and I set against each transaction type a multiplier of 1 or -1 to multiply against the quantity. So a goods received transaction would be 1, stock issues, goods returned etc would be -1

 

[MelissaMuz]

your system says you have 100 of an item in stock. A physical stock check is done and only 98 are counted - so the shrinkage is 2. Personally I use the phrase 'stock check adjustment' because when you count you might find you have 103. Doesn't sound like it would apply in your case but the other 'shrinkage' could be due to stock going out of date.

Either way I agree with Doc - one table for transactions with a field to indicate type of transaction. You can even include the project required quantities as a transaction - just modify your query to calculate current stock to ignore them. Assuming you include a project ID field you can then easily determine what stock has been issued to what project and how much is still required.

If most of your data is manually entered, I just store a positive number (easier for the user) and I set against each transaction type a multiplier of 1 or -1 to multiply against the quantity. So a goods received transaction would be 1, stock issues, goods returned etc would be -1

That makes sense, thank you.

 

[CJ_London]

Just as an aside, when you have a single transaction table that simplistically covers both goods in and goods out you can still link to the relevant detail tables without destroying referential integrity. Very simplistically you might have a transactions table like this

tranPK....trantypeFK...OrderFK...supplierFK...stockFK....Qty...tranDate
1...............1......................null...............2.......................3..............100...1/1/22----general receipt of a delivery
2...............2......................4....................null..................3...............10.....2/1/22----stock assigned to order 4 (you can get customer from the order)
3...............2......................5....................null..................3................20.....3/1/22---stock assigned to order 5 (you can get customer from the order)
4...............1......................6...................3........................4................15....4/1/22---stock received on special order for order 6 (you can get customer from the order)

you always need to complete tranPK, trantypeFK, stockFK, Qty and tranDate
you only need to complete OrderFK and supplierFK if required (trantype tells you which needs to be completed), providing you otherwise leave as null, you won't destroy referential integrity

 

[MelissaMuz]

Just as an aside, when you have a single transaction table that simplistically covers both goods in and goods out you can still link to the relevant detail tables without destroying referential integrity. Very simplistically you might have a transactions table like this

tranPK....trantypeFK...OrderFK...supplierFK...stockFK....Qty...tranDate
1...............1......................null...............2.......................3..............100...1/1/22----general receipt of a delivery
2...............2......................4....................null..................3...............10.....2/1/22----stock assigned to order 4 (you can get customer from the order)
3...............2......................5....................null..................3................20.....3/1/22---stock assigned to order 5 (you can get customer from the order)
4...............1......................6...................3........................4................15....4/1/22---stock received on special order for order 6 (you can get customer from the order)

you always need to complete tranPK, trantypeFK, stockFK, Qty and tranDate
you only need to complete OrderFK and supplierFK if required (trantype tells you which needs to be completed), providing you otherwise leave as null, you won't destroy referential integrity

This is all very helpful thank you, could you just confirm what PK and FK stand for?

 

[CJ_London]

PK=Primary Key - all tables should have one and is normally an autonumber field
FK=Foreign Key - all child tables should have one and it contains the PK value of the Parent record - if PK is autonumber then the FK will be a long

 

[MelissaMuz]

Oh of course! I should have guessed that! Thank you.