cross table subtraction with a “where” clause

[TheEvilSam]

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|_______|__________|_________|Clsbrg|_____2.70|
23_______|________1|_______|__________|_________|Clsbrg|_____2.70|
24_______|________1|_______|__________|_________|Clsbrg|_____2.70|
25_______|________1|_______|__________|_________|Clsbrg|_____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 minused form 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

 

[NickHa]

Here's a rudimentary VBA routine which may give you some clues:
Private Sub updateStock()
Dim rstCurrentSale As Recordset, rstDraughtStock As Recordset
Set rstDraughtStock = CurrentDb.OpenRecordset("SELECT * FROM tblDraughtStock;")
Do Until rstDraughtStock.EOF
Set rstCurrentSale = CurrentDb.OpenRecordset _
("SELECT DISTINCTROW Count(*) AS SaleCount FROM " & _
"tblCurrentSale GROUP BY DraughtID HAVING DraughtID=" _
& rstDraughtStock!DraughtID & ";")
If Not rstDraughtStock.EOF Then
CurrentDb.Execute "UPDATE tblDraughtStock SET Stock = " & rstCurrentSale!SaleCount _
& " WHERE DraughtID=" & rstDraughtStock!DraughtID & ";"
End If
rstDraughtStock.MoveNext
Loop
End Sub
Things to watch out for:
1. think about how to prevent double updates.
2. on the EXECUTE statement, you probably need some validation to ensure you have a current record to update (or put some error traps round the statement)