Results 1 to 3 of 3
  1. #1
    theevilsam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    19

    cross table subtraction with a “where” clause

    so I have a number of table in my access database. one of the tables is called "tblCurrentSale" and another is called "TblDraughtStock"



    The current sales table has the following layout

    CurrSaleID| DraughtID| SpiritID| SoftDrinkID| BottleID| Item__| SalePrice|
    22_______|_______1|_______|__________|_______|_Cls brg_|____2.70|
    23_______|_______1|_______|__________|_______|_Cls brg_|____2.70|
    24_______|_______1|_______|__________|_______|_Cls brg_|____2.70|
    25_______|_______1|_______|__________|_______|_Cls brg_|____2.70|

    TblDraughtStock looks as follows

    DraughtStockID | DraughtID | Item | Stock| MinLevelStock |
    ____________1_|_______1_|Clsbrg_|5000_|________500 __|
    ____________2_|_______3_|Crling_|5000_|________500 __|
    ____________3_|_______6_|__IPA_|5000_|________500_ _|
    ____________4_|_______7_|Wrthy_|5000_|________500_ _|


    I need some VBA or SQL that will subtract 1 from the stock column in "TblDraughtStock" when the draughtID is the same in both "tblcurrentsale" and "tblDraughtStock" ie, in the example above, on clicking the complete sale button, 4 would be subtracted from the stock column of the "TblDraughtStock" where Draught ID = 1 giving the new table

    DraughtStockID | DraughtID | Item | Stock| MinLevelStock |
    ____________1_|_______1_|Clsbrg_|4996_|________500 __|
    ____________2_|_______3_|Crling_|5000_|________500 __|
    ____________3_|_______6_|__IPA_|5000_|________500_ _|
    ____________4_|_______7_|Wrthy_|5000_|________500_ _|


    Any help would be appreciated. Further info if needed can be given.

    Thanks

    Sam

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Generally I wouldn't try to store the quantity on hand. There's a discussion here:

    http://allenbrowne.com/AppInventory.html

    I've written inventory applications, and I just calculate on hand from transactions. If you want to do it, he gives some guidance at the bottom of that link.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    theevilsam is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    19
    That seems really difficult. this is supposed to be an epos system that can be used in a pub so there are going to be vast amounts of sales going through on a daily basis. I have an admin form which has restocking buttons which add to the stock on another database which I can bring across from another database.

    The problem is that there can be a large number of Items in the current sales table. There can also be numbers of the same item (3 Carling for example)

    I don't really have enough experience in this area.

    I have tried to use
    Code:
    SQLStock = "UPDATE TblDraughtStock t INNER JOIN tblCurrentSale c ON c.DraughtID = 1 SET t.Stock = t.Stock - 1 WHERE t.DraughtStockID = 1"
    but this did not work, and I would have to make the SQL statement 12 times as long to include "IF" for each of the drinks

    would someone be able to help me do a stock check for my on hand. it think this could be much more interesting as long as I can get reports for it. I'll upload my project now and if I could be pointed in the right direction I would be most grateful. I just couldn't see how to make it work from what was shown on the link.

    Thanks

    Sam

Please reply to this thread with any new information or opinions.

Similar Threads

  1. subtraction between records
    By JJCHCK in forum Programming
    Replies: 5
    Last Post: 10-11-2011, 12:57 AM
  2. cross table problem
    By humanmaycry in forum Queries
    Replies: 3
    Last Post: 07-20-2011, 12:08 PM
  3. Using the TOP clause
    By WSlepecki in forum Queries
    Replies: 1
    Last Post: 04-08-2011, 06:59 AM
  4. Cross tab help
    By A S MANN in forum Queries
    Replies: 5
    Last Post: 10-28-2010, 07:46 AM
  5. Replies: 4
    Last Post: 03-12-2010, 05:42 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