Auto-population of table (1 Viewer)

[Darrell]

Hey guys, bit stuck on this problem....

I want to do a simple stock control function based on three areas; Stocktake, goods inwards and sales.

The bit I am stuck on is the stocktake part.

Example: I have a products table with say 10 items in it called in this case X1 to X10. The stock can be 'owned' by two different parties being A or B

So if I do a stocktake and the results are

Part......Owner......Qty
X1.........A..............1,000
X2.........A..............1,000
X3.........A..............1,000
X5.........A..............1,000
X5.........B..............1,000

What I want to do then is populate the Stocktake table with the remaining 6 records for Owner A and 9 records for Owner B with a zero value when the user closes the Stocktake input form.

Any help is greatly appreciated

 

[CJ_London]

you need an insert query something along the following lines

 INSERT INTO [StockTake] (Part, Owner, Quantity)
SELECT Products.Part, Products.Owner, 0
 FROM Products LEFT JOIN [Stocktake] ON (Products.Part= Stocktake.Part) AND (Products.Owner= StockTake.Owner)
WHERE StockTake.Part Is Null

This assumes your product table has part and Owner fields.

If you product table does not have an owner field you will need something like this, run twice, once for each owner

 INSERT INTO [StockTake] (Part, Owner, Quantity)
SELECT Products.Part, [Enter Product Owner], 0
 FROM Products LEFT JOIN [Stocktake] ON (Products.Part= Stocktake.Part) WHERE StockTake.Part Is Null OR StockTake.Part <>[Enter Product Owner]

 

[Darrell]

Hey CJ, thanks very much for that, it has solved my problem perfectly.

Cheers