Calculating Stock Amounts

[BobTheBuilder]

Hi,

I'm trying to create a way to calculating stock levels in a database.

I want to be able to place an order in tblOrders for 2 PC's and have my database decrement the stock amount in tblStock of 3 record fields (motherboard, RAM, network card)....... I need the stock amount to reduce amounts according to how many PC's ordered.

Can anyone please help?!

 

[MarkK]

- tblStock? What data goes in table stock?
- Typically the quantity on hand is a the sum of what was purchased minus the sum of what was sold. So the quantity you have on hand is a concept like a bank balance which is not a stored value--it is the result of a calculation.
- See it? If you wrote accounting software you would not have a field for the balance of an account. All you would have is a series of transactions for various amounts, and the balance of the account at any point in time is a calculation on all the previous transactions.
- In an inventory system I would create PurchaseOrder and SalesOrder objects, and the detail lines of these items would specify a quantity, a price and a link to a product. To calculate stock on hand for a product I would subtract the sum of quantities sold from the sum of quantities purchased, and I would create spoof Sales/Purchase orders to create inventory adjustments, so I would essentially sell to or buy from a mock customer called inventory adjustment.
- Makes sense?
Mark

 

[BobTheBuilder]

Hi,
thanks for the message, does make sense.... only that its not a database that calculates transactions, but is more to monitor production; therefore stock levels are monitored and caluculated in relation to the volume of orders placed.

the tblStock is simply there to hold the values of stock.

so if 2 or 3 PC's were ordered 1 Motherboard x2/3, 2 1GB RAM x2/3 and 1 network card x2/3 would be subtracted, updating the current amount in stock

i hope that made sense.. any advice on the expression i would need to generate to get this to action? i'm using MS Access

 

[MarkK]

What exactly do you need to do? Are you just asking how to edit a value in a field in a table?

 

[BobTheBuilder]

I want to edit values in several fields in tblStock dependant on order quantity placed in tblOrders

 

[MarkK]

There basically two ways to edit data in a table. Open a recordset and edit data it contains, or run a SQL update query.
To open a recordset you'd use code like..

dim rst as dao.recordset
set rst =currentdb.openrecordset( _
  "SELECT Field1, Field2 " & _
  "FROM Table1 " & _
  "WHERE Field0 = " & SomeValue)
With rst
  .edit
  !Field1 = NewValue1
  !Field2 = NewValue2
  .update
  .close
end with

To use an update query you would use code like ...

currentdb.execute _
  "UPDATE Table1 " & _
  "SET Field1 = " & NewValue1 & ", " & _
    "Field2 = " & NewValue2 & ", " & _
  "WHERE Field0 = " & SomeValue

Is the kind of thing you are looking for?
Cheers,

 

[BobTheBuilder]

ah I see.. looks exactly like code that would help for sure.

I think where it gets more complex for my required code is the a calculation similar to:

amount = amount - (quantity * 2)

if that makes any sense?

 

[MarkK]

OK, so to update a field in a table with the results of a calculation you could modify the above code as follows (and I'll stick with the recordset since it is a more generally useful structure to become familliar with)...

dim rst as dao.recordset
set rst =currentdb.openrecordset( _
  "SELECT Field1 " & _
  "FROM Table1 " & _
  "WHERE IDField = " & SomeID)
With rst
  .edit
  !Field1 = !Field1 - (quantity * 2)
  .update
  .close
end with

So that code assigns the value of an expression to a single field in a single record. The record was identified by its unique ID.
And you can start to see what a recordset provides. Where a table provides permanent data storage on disk, a recordset provides immediate in-process data manipulation.
Hope you're finding this useful,
Mark

 

[BobTheBuilder]

Thanks Mark, I've found this extremely helpful!!

 

[MarkK]

You bet, all the best!

 

[Lightwave]

Bob

You might find this thread worth reading as well

http://www.access-programmers.co.uk/forums/showthread.php?t=218467

 

[DevastatioN]

Bob,

As lagbolt has already suggested in his first post, there is a definite design issue in the way your database is built.

Quantities should never be stored, but calculated. Although this code helps you do what you want to do, the data can easily desync if any inputting errors are made (for example, you enter 5 computers instead of 4... when you go back and change 5 to 4, you need to make sure you adjust the tblStock manually as well!).

You have tblOrders which decreases from the stock (good). If you had another table for when you stock is incoming, for example tblPurchases (you purchase the stuff, then orders is you selling the stuff). Then stock will always be tblPurchases total - tblOrders total, for each item. As a report, or query, however you like.

This insures that you data is always in sync, and properly reflects the information in the database. I would consider looking at your design to make sure it is properly normalized.