Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23

    Unanswered: alert when products in stock fall below a certain level (was "Is this possible")

    Any help that can be given here would be much appreciated.

    I have a form with a subform. The subform shows the stock ordered and sold. On the main form it calculates the number of products on order and the no of products in stock, pretty much the same as the Stock Management database that microsoft offer as a free download.

    What I need to do is create an alert when the products in stock fall below a certain level.

    For instance if I had 50 items in stock, I might want to be told by a pop up when I get to 20 items that I am running short of stock and will need to re-order.

    I thought of creating a query, but am at a loss as to how to do this when the calculation on the form is not stored in any tables.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd break the problem into two parts..

    first the easy bit a query which calculates if your stock is below a minimum threshold.. id suggest you base the threshold on a value per product rather than a blanket figure for each item. as time progress, you cold then alter your figures based on some form of statistical analysis of what the stock for each item should be (often that comprises say 110% of sales in the last time period (say the order lead time was 3 months, then the trigger level might be averaged sales over the 3 month period times 1.1), you may want to build in some seasonality, depending on what you sell. if for example you sell Christmas decorations and you are a wholesaler, then its likely you will do a roaring trade in Christmas decorations in say September, through to November and then virtually none till next year.. in that even you need to change your demand forecast to reflect that seasonality. you could also build in say a warning level say 125% to indicate to the buyer that you are approaching the re-order threshold, so they could using their experience use that item to bulk up an order if say the supplier required a minimum order size.

    next the hard part, when to trigger it.
    various options... you could send an email to the buyer when a transaction pushes the product into the re-order zone.. so it could be triggered by any sale.. ie when a sale is made

    you could run a daily/weekly report/form for the buyer indicating items which are currently requiring attention

    ..personally Id go down the latter route.. a form which the buyer works form. you could add to that process by say allowing for 'virtual' stock.... ie the buyer adds to the data that they have ordered x items, due for delivery in xx/xxx/xxxx. that way round if you run out or low on stock you can tell the customer immediately that the new products are coming in.

    it has the advantage that the buyer isn't going to be snowed under by email notifications, the system will handle returns, so if a customer returns products they can go back into stock, and should no longer appear on the action required form.

    so the query is fairly simple
    sum all orders + sales for an item and subtract that from all deliveries fro that item.. that gives you the current balance.

    Id suggest you go down a transactions route to calculate your free stock.
    Items that lead to stock increasing are:
    purchases from suppliers, returns form customers, stock adjustments (say you did a recount and fond more items than expected in the stores)
    Items that lead to stock decreasing are:
    sales to customers, orders from customers not yet fullfilled, losses due to damage or theft, adjustments because there is less in stock than you expected.

    for performance reasons some places will run a periodic stock count so they may run their transaction summary from the date of the last stock take.
    ie current free stock = opening balance on last stock take + from that date on (all deliveries & customer returns - all sales & supplier returns).
    thats perhaps a sophistication you don't need at first, but if you make your stock query a simple bit of SQL, then you can develop the sophistry as time and your experience develops. it could be that you decide to implement the
    calculate stock action as a function, rather than a single SQL query

    HTH
    Last edited by healdem; 06-24-07 at 05:14.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2007
    Posts
    277
    Healdem,
    Good well thought out reply! You may however wish to look up the word "sophistry" because I believe you meant something else. Still an excellent reply.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by RuralGuy
    Healdem,
    Good well thought out reply! You may however wish to look up the word "sophistry" because I believe you meant something else. Still an excellent reply.

    I thought sophistry and statistical methods go hand in hand
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2007
    Posts
    277
    Is this your understanding of sophistry? http://dictionary.reference.com/search?q=sophistry&r=5
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by RuralGuy
    Is this your understanding of sophistry? http://dictionary.reference.com/search?q=sophistry&r=5
    Yes
    ...Itsafaircopyerhooner
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2007
    Posts
    277
    Fair enough. Good talking to you.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  8. #8
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    Many thanks Healdem for your reply. Very useful and informative. I shall be keeping things very simple to start off with. At present I just need to create an alert when the stock is low and advising the user to order more. Over time it will get a bit more complex.

    I guess this thread is dead now though as it now seems to be discussing sophistry, whatever that is. I've noticed it means many things depending where you search for the word.

    Wikipedia gives the following:
    The term sophism originates from Greek sophistes, meaning "wise-ist", one who "does" wisdom, one who makes a business out of wisdom (sophós means "wise man").

    In modern usage, sophism, sophist, and sophistry are derogatory terms.

    A sophism is a specious argument used for deceiving someone. It might be crafted to seem logical while actually being wrong, or it might use difficult words and complicated sentences to intimidate the audience into agreeing, or it might appeal to the audience's prejudices and emotions rather than logic. The goal of a sophism is often to make the audience believe the writer to be smarter than he/she is.

    A sophist is a user of sophisms, i.e. an insincere person trying to confuse or deceive people. Sophists will try to persuade the audience while paying little attention if their argument is logical and factual.

    Sophistry means making heavy use of sophisms. The word can be applied to a particular text or speech riddled with sophisms.

  9. #9
    Join Date
    Mar 2007
    Posts
    277
    I'm afraid I still do not see how sophistry and statistical methods go together but then my mind is not what it used to be.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Off topic: sophistry

    doing a google using "define: sophistry" comes up with the following

    # sophism: a deliberately invalid argument displaying ingenuity in reasoning in the hope of deceiving someone
    wordnet.princeton.edu/perl/webwn

    # Sophism was originally a term for the techniques taught by a highly respected group of philosophy and rhetoric teachers in ancient Greece. The derogatory modern usage of the word, suggesting an invalid argument composed of specious reasoning, is not necessarily representative of the beliefs of the original Sophists, except that they generally taught Rhetoric. ...
    en.wikipedia.org/wiki/Sophistry

    personally I don't trust wiki, unless its corroborated by another reputable source.. but thats my bag.

    now to me given that careful choice of the statistical method, and datasets you can generate a statistically valid result to prove virtually any point, as any politician, their spokespeople or the media may care to make. hence in my books sophistry and statistics often go hand in hand. its when you start getting involved in applying statistical methods to stock & sales forecasts.. thats when you have to be very very careful. thats when if you are not reviewing the true position you can end up with significant over or under stock. Ive seen it at both ends of the spectrum, I've seen statistical models which failed to get stock in for seasonal items, Ive seen similar models trying to get more stock in of items which have suddenly started selling fantastically (the model didn't take into account that the price had been dramatically reduced to clear out the warehouse of an item that wasn't selling at a significantly below cost price

    but to return to the topic daveyy please come back if you want to develop your ideas further, thats what we are supposed to be about

    mu apologies, I can't help it but I seemed to swallow a dictionary when I was about 18..... It hasn't helped my spelling or typing that much though
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2007
    Posts
    277
    <sound of slapping forehead>
    Now I understand and agree. Thanks!
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

Posting Permissions

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