Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012

    Unanswered: Please help , Newbie here, need sql help for access 2010

    Hiya guys,

    I am creating a database for a ***** club. The tables involved in this sql I have are : current stock levels, and Stock waste.

    Once a new record has been inserted into Stock waste I want to run an append query to deduct this amount of waste from the current stock levels Table.

    here is what I have so far :

    UPDATE (Stockitem INNER JOIN [Current Stock Levels] ON Stockitem.[Item ID] = [Current Stock Levels].[item ID]) INNER JOIN Stockwaste ON Stockitem.[Item ID] = Stockwaste.[Item ID] SET [Current Stock Levels].[Amount In Stock] = [Current Stock Levels]![Amount In Stock]-[Waste amount]
    WHERE (([Stockwaste]![Waste date]=Now()));

    If the WHERE clause was not included this SQL works but deducts every item in the Stockwaste table from the Current stock levels table. This is not what I need

    I need to only deduct a record in the Stock waste table from the current stock levels table if the date only matches today's date (=NOW()). As I only want to deduct that day's waste.

    Please help, and thankyou for reading.
    I appologisde If I have put this in the wrong section as i am a newbie
    many thanks

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    redesign your tables

    a table for products
    a table for transaction types (and how they affect stock)
    a table for transactions (which identifies what the stock mov ement was, both quantity and transaction type

    transtype identifies a legend (eg sales, stock transfer out, transfer in, wastage etc... the direction (often + or -1) indicates what effect the transaction has (ie if +1 then it adds to stock, if -1 then it subtracts from stock

    then to find current stock you run a SQl query that multiplies the quantity in the transaction by the direction and sum the result

    you have a potential issue with stock control if say you do a periodic stock count. you can handle that by either creating a stock transfer of the appropriate direction. OR create a transaction record with a special status and only sum there on.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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