Auto-population of table (1 Viewer)

Darrell

Registered User.
Local time
Today, 02:22
Joined
Feb 1, 2001
Messages
196
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

Super Moderator
Staff member
Local time
Today, 02:22
Joined
Feb 19, 2013
Messages
12,454
you need an insert query something along the following lines

Code:
 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

Code:
 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

Registered User.
Local time
Today, 02:22
Joined
Feb 1, 2001
Messages
196
Hey CJ, thanks very much for that, it has solved my problem perfectly.

Cheers :)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom