05-07-12, 11:08 #1Registered User
- Join Date
- May 2012
Unanswered: Please help , Newbie here, need sql help for access 2010
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
05-07-12, 13:37 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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