Alpha DevCon 2018
Results 1 to 5 of 5

Thread: On Hand Calculation

  1. #1
    Member
    Real Name
    Josh Perelman
    Join Date
    Nov 2005
    Posts
    16

    Default On Hand Calculation

    I am updating a database that has been working for a couple of years and have realized the short sightedness of my initial ways. While the information is valuable....had it been set up correctly in the beginning, it would be even more so. I am trying to come up with a real time calculation for inventory. I want the calculation to be stocktake.count from a specific date and all PO.qty for quantity purchased after the specific date less InvoiceDetail.qty from the specified date. Should this calculated field be supplied to a field in the Product table, i.e. QtyOnHand or used for a calculation for a query. I would rather do it correctly this time and hopefully not have to dramatically overhaul in the future.

    Also, how do people handle goods that are on hand but allocated to an order. This would be useful if we have a rush job that requires a specific item that is due before another job that uses the same item, but would allow enough time to order additional product without incurring a rush charge.

  2. #2
    Member SNusa's Avatar
    Real Name
    Robert Tupper
    Join Date
    Dec 2007
    Location
    Northeast, USA
    Posts
    893

    Default Re: On Hand Calculation

    Sounds like you're talking about getting totals from trying to combine "posted" (previous months end inventory) + "non-posted" (this months purchases) - "non-posted" sales......

    I used an accounting system for many years that tracked inventory as follows:
    2 fields in inventory record: OnHand and Committed.
    OnHand was a "real-time" value that included your "stocktake.count" + "PO.qty" (actually merchandise received - merchandise returned)....
    Committed was the current period sold quantity. (also updated in "real-time")
    Data entry (optionally) prohibited negative on-hand sales by using these two fields together.
    At the end of the month, when modules were posted, the Committed field was zeroed out, and subtracted from the OnHand value in the inventory table.

    Seems to me, that if you have large tables.... Running queries that include calculated field totals using dbsum() etc. (in the fields, or in the query definition) will both severely hinder performance... You'd eventually end up with a very poorly performing system. So I would keep these values in the items table, and update them in "real-time", when merchandise is sold and received.....
    Last edited by SNusa; 04-14-2012 at 09:16 PM.
    Robert T. ~ "I enjoy manipulating data... just not my data."
    It's all about the "framework." (I suppose an "a5-induced" hard drive crash is now in order?)
    RELOADED: My current posting activity here merely represents a "Momentary Lapse Of Reason."

  3. #3
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    3,995

    Default Re: On Hand Calculation

    Josh, I built a stock control and invoicing suite for a motorbike repair shop. In fact I've just completed an Invoicing app which handles payments/part payments/refunds etc.

    The easiest way to manage stock values is to have the data held in a table. It's not Normalised, but a heck of a lot easier to work with and report on.
    So,
    On Hand = Physical Stock
    Committed
    ..Linked to a particular order/job
    Receipts
    ..Linked to On Hand

    Then,
    On Receipt or Sale, post quantity to On Hand (+or-)
    Available = On Hand - Committed

    Few things to think about.
    1) Returns/Cancellations
    2) Min Stock Levels. I had a warning notice and a report run nightly indicating items getting close to Min Qty, and also had an e-mail reorder on suppliers module.
    3) Handcuffs. When people take stock and don't record it, they need handcuffing to something heavy.
    4) Just remembered, I also created an Update process which took values and reapplied them to the stock table from the StockTake date. That should ( but rarely does ) evaluate to the stock you can see and touch. It helps when users get confused about adding/selling/refunding items.
    Ted Giles
    Example Consulting - UK
    .

    https://exampleltd.example-software.com/
    See our site for Alpha Support, Conversion and Upgrade.

  4. #4
    Member
    Real Name
    Josh Perelman
    Join Date
    Nov 2005
    Posts
    16

    Default Re: On Hand Calculation

    Ted,

    So if I understand you correctly, are you describing a transaction table for inventory with date, transaction type, quantity, etc or several tables?

    Thanks for the help.

  5. #5
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    3,995

    Default Re: On Hand Calculation

    A Transaction Table Josh.
    Each stock item would have a transaction against it (for audit purposes as well).
    Incoming stock would post the Qty
    Adjustments as a result of shrinkage would be password protected
    Who did what and when.

    The Stock table could hold the posted adjustments from the Tran Table.
    Are you OK with Posting Rules?
    Ted Giles
    Example Consulting - UK
    .

    https://exampleltd.example-software.com/
    See our site for Alpha Support, Conversion and Upgrade.

Similar Threads

  1. Cursor as a hand
    By Turnbullca in forum Application Server Version 10 - Web/Browser Applications
    Replies: 3
    Last Post: 02-11-2010, 06:41 AM
  2. Stock On Hand Issue
    By stabba in forum Alpha Five Version 6
    Replies: 5
    Last Post: 03-12-2007, 08:33 PM
  3. using a hand held barcode scanner
    By Artur Bento in forum Alpha Five Version 4
    Replies: 3
    Last Post: 12-12-2003, 08:12 AM
  4. Left Hand, Right Hand
    By dik_coleman in forum Alpha Five Version 5
    Replies: 5
    Last Post: 12-12-2003, 05:01 AM
  5. I'd like to buy a 2nd 'hand version'
    By Glen Chambers in forum Alpha Four Version 6 and Prior
    Replies: 2
    Last Post: 10-04-2000, 07:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •