Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17
  1. #16
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have no such file but you can find lots of examples. One issue for me is that it's not clear where your head is at with respect to storing this calculation in the first place. Sounds like you are bent on calculating the qoh and storing that though, and that I won't help with. Reason being, it's not something I'd do and if I proffer a method and you need to tweak it later, I don't want to feel obligated to help you fix my idea when you shouldn't be doing it anyway.



    If you get stuck in your research, a pic of your relationships window (if you've established them) might shed some light.

  2. #17
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    Location
    USA
    Posts
    64
    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.
    In general you could do a physical inventory on a specific date and store the data for an item in your tblStock table like this...
    tblStock:
    StockID - This was input long ago
    StockDescr - This was input long ago
    StockReorderPoint - This was input long ago
    StockInventoryQty - UPDATE THIS FIELD DURING PHYSICAL INVENTORY!
    StockInventoryDate - UPDATE THIS FIELD DURING PHYSICAL INVENTORY!

    And DON'T even include a StockQOH field in the above table.

    And you've got tblOrder (or perhaps some of this is in a tblOrderDetail table):
    OrderID
    OrderDate
    OrderCustNum
    OrderStockID
    OrderQuantityOrdered

    Then perhaps on your Stock Input page or a report or whatever for a particular item you could easily show the StockReorderPoint field and you could also calc a QtyOnHandCalc textbox with a ControlSource of something like this...
    =Nz([StockInventoryQty])-Nz(DSum("[OrderQuantityOrdered]", "tblOrder", "OrderDate>=#" & [StockInventoryDate] & "#"))

Page 2 of 2 FirstFirst 12
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