Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56

    How can i use a field in a form to do a calculation in a table?

    I have an order form that has a field called "QuantityOrdered" from the tblOrders table and i have a table called tblStock that has parts that are in stock with a field called QOH (Quantity On Hand).
    So what I want to do is have the quantity entered into QuantityOrdered deducted from the QOH in Stock.
    How do i go about doing this?


    Whats the best way to do this. In a flat database we used to have to do an end of day that would do all the calculations. I prefer this be automatic. Should it deduct the amount at the end of all the data entry. Maybe have a button then says something like "place order" that will run the script? Or should it do the calculation on each line item as you start the next?
    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    QOH is an aggregate calculation. Only in rare cases should aggregate data be saved. It should be calculated when needed. Review http://allenbrowne.com/AppInventory.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Maybe Im misunderstanding you. Wouldnt I want the order to reduce the qty in my stock table? If not whats to keep people from selling it again when there is no stock? Will the link you gave me explain the process to remove the sold stock from inventory? Actually, I just read it. Interesting. A lot to wrap my head around. I can understand how doing it the way i wanted could cause errors. Thats a lot to wrap my head around though. What would be the best way to do it simply like I wanted. Its a small Co. only 3 employees

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A saved QOH value is not supported by data and it cannot be validated. Auditor's take exception to such situation.

    Code for actually changing a saved value is not terribly difficult.
    Hard to advise specifics without knowing your db but could be something like:

    CurrentDb.Execute "UPDATE tblStock SET QOH = QOH -" & Me.Qty & " WHERE StockID=" & Me.StkID

    Real trick is figuring out what event(s) to put code in. Yes, your 'Place Order' button seems to be a reasonable place for this.

    Then you have to deal with situation of deleting or editing existing record and changing QOH.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Have you some technology which teleports goods to client the moment the order is filled?

    And don't forget, the quantity client ordered from you or you ordered from client by various reasons may be different from quantity you get or send out at given date.

    You must have a table like tblStockTransactions: StockTransactionID, TransactionDate, TransactionType, DocType, DocNo, PartID, PartQty, ...
    where TransactionType has values 1 (incoming) or -1 (outgoing), DockType has values like 1 (purchase order), 2 (selling invoice - which usually is based on client order), 3 (production order - which may be based on client order in case you are subcontracting), ..., 8 (scrapping act), 9 (inventory correction). The quantity of part in stock will be sum of TransactionType*PartQty over all transactions for this part (or quantity at last inventory + sum of TransactionType*PartQty over all transactions after last inventory for this part). Transactions are registered in stock whenever parts arrive, or are taken/sent out.

    Then of-course you need table tblParts; table tblDocuments with field DockType or tables like tblPurchaseOrders, tblSellingInvoices, ...; and when you have several different stockages, then tblStockages (and accorting field in tblStockTransactions).

    In case you are making stock inventories, the you also need tables like tblInventories (basically a table with inventory dates) and tblPartInventoryResults (with real quantity in stock at inventory date for every part, and a field for setting a flag when a correction is made in tblStockTransactions). Whenever a difference between calculated quantity at inventory date and inventory quantity exists, a correction must be registered in tblStockTransactions (you may have field(s) in tblPartInventoryResults where the reason for this difference is explained).

  6. #6
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Actually we just complete our very first trail run on the PartTeleporter. Had a few wrinkles that still need worked out but overall it was mostly successful.
    Seriously though, if I have 5 sales people and lets say a certain part gets hot and a sales person takes an order for the full quantity on that part, what keeps sales persons 2-5 from selling same said part to which there is no mas? I
    Are you saying that the inventory should get moved to a different table once an order is placed via phone etc.. Like PartsPO then once its shipped it get sent to another table like PartsInvoiced..??
    Sorry if this is not 100 percent sinking in yet. Im fairly new to this concept. We always just considered sold once we had a PO, and to keep someone else from selling it, reduced the qty to zero at PO time.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    what keeps sales persons 2-5 from selling same said part to which there is no mas?
    Validation and calculation on your form (not in the table).
    You can show qoh as the beginning balance minus all outs + all ins and show (e.g.) 8. You don't allow a new order qty greater than 8 even though there might physically 20 of them on the shelf. Using these design principles, you can even "reserve" quantities - either of in stock items (say for work order planning if that were your business) or future deliveries, although that's starting to get complicated.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by wcook101 View Post
    ..if I have 5 sales people and lets say a certain part gets hot and a sales person takes an order for the full quantity on that part, what keeps sales persons 2-5 from selling same said part to which there is no mas?
    One possible solution is you have additional field in tblStocTransactions which indicates that some qty of part is really moved, or it is reserved. When sales person registers selling invoice, an entry is automatically added into tblStockTransactions, which reserves invoiced parts automatically. After invoice is paid or confirmed (depending on agreement with client), stockage is allowed to send purchased goods out. When sending out is registered, then reserving entries linked to this invoice are changed to moving out entries. Or - in case you want tblStockTransactions table work like log file (no entries aren't deleted or edited ever after they are created) - a procedure adds opposite reserving entries to nullify reserving, and adds according moving out entries.

    The main thing is, no part is registered as moved out/in unless it is moved out/in really. When the quantity in stock is calculated, reserved entries are ignored, when available quantiy is calculated, reserved entries are taken into account. When an invoice is registered, available quantities for all invoiced parts must be checked, and registering blocked - or at least sales person must be warned, in case there is deficit in stock for some of them.
    Last edited by ArviLaanemets; 02-04-2020 at 03:28 AM.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if I have 5 sales people and lets say a certain part gets hot and a sales person takes an order for the full quantity on that part, what keeps sales persons 2-5 from selling same said part to which there is no mas?
    you calculate on the fly at the time an order is taken. In real terms this means you do it twice. The first time is when your sales person selects the item being sold to determine the available stock (perhaps a product combo afterupdate event or quantity update on your order subform). The second time is just before the order is created to confirm there is still available stock (perhaps the order subform beforeupdate event). Depends on how your form works.

    It all comes down to timing and your business rules. If sales person A starts an order for product X and takes a short break before finishing it and in the meantime sales person B takes and completes an order. When sales person A returns to complete the order, they will be advised on the second event that the order now has insufficient stock when they try to complete their order.

    Your business rules need to clarify exactly when stock is committed and no longer available- at the time an order is created but not committed (the first event), after it is committed (the second event) or some other event such as when actually shipped/paid for etc.

    My preference is to keep all transactions (in/out/adjustments/stocktake/reserved) in one table.

  10. #10
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Ok. I think I get it. Im going to add different fields for stages of inventory movement. Im not going to go overboard, just pre order/post order. That should create enough of a trail for me. Thansk

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Ok. I think I get it. Im going to add different fields for stages of inventory movement.
    Maybe not. If inventory movement is 1 "thing" (sounds like it is) then it requires 1 field - no more. Its values identify what that movement is. You seem to be using your Excel brain to design databases, which is not helpful.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    Its funny you say that. The last database I created was with Q&A back in 1994 which was flat and worked amazingly well and still does in a DOS box, and I love banging stuff out in Excel. Sometimes I have to walk away and do something else for a while, then Im ohhh I get it.
    So Im going to create a field in stock called QuantityOrdered and it will pull the quantity out of QOH as the order is booked. I'll also create a field QuantityShipped as it is shipped and use that for the Quantity on the invoice. that way parts wont mysteriously come up missing as there should be a path to where they went. I think that covers everything but returns. I'll just have that as QuantityReturned put the stock back into QOH. I think that covers it.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    in the end you have to go with your own decision I guess. It's not an approach I would take (reducing the count of QOH field). What if you need to reduce the count because of losses - have a field for that too? One for sold, one for damaged, one for miscellaneous reasons? If that would be your approach, things may work for some time, but then getting data in and out (e.g. for reporting) becomes quite difficult.

  14. #14
    wcook101 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    56
    can you show me a working model of how you would do it so i can play with it and see exactly what you guys are talking about, because I interpretted that what you suggested was making additional field to pull QOH into as it moves along its way to the customer.

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps take a look at the Inventory db template

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 08-11-2016, 08:23 PM
  2. Replies: 2
    Last Post: 06-17-2015, 09:08 PM
  3. Replies: 1
    Last Post: 12-04-2014, 01:43 AM
  4. Automatic calculation of table field (A2003)
    By GraeagleBill in forum Programming
    Replies: 9
    Last Post: 04-20-2013, 12:06 PM
  5. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums