CODEWITHSUNDEEP
sqlms-accessvba
Sam 'Evilsam' Spiller
Sam 'Evilsam' Spiller

Reputation: 129

inventory summary Access

Well after much messing about I have finally got a query that gives sales totals of all the products, and another that gives stock absorbed for all of the products, (images below).

I was looking at Allen Brown's stuff and I get it, but I was wondering If i could make a summary report covering all of the available stock for all of the products. there are 31 products in total and they all appear in the stock query and in the sales query.

http://imageshack.us/photo/my-images/810/87887.jpg

http://imageshack.us/photo/my-images/827/8787j.jpg

Any Ideas on the coding to use...

I guess it would be possible to do, but then I don't really know where to start

I really want to make a report which will summarise the stock of each of the products in the queries.

Instead of having a button for working the stock of one specific product, having a button that will work the stock for each of the products in the queries at the same time.

does this make sense?

Thanks

Sam UPDATE

this is the query for finding stock

SELECT TblStock.ProductID, Sum(TblStock.StockLevel) AS Stock, TblProduct.Item
FROM TblStock INNER JOIN TblProduct ON TblStock.ProductID = TblProduct.ProductID
GROUP BY TblStock.ProductID, TblProduct.Item;

this is the query for finding quantity of sales

SELECT TblProduct.Item, Sum(TblTotalSale.Size) AS Quantity, TblProduct.ProductID
FROM TblProduct INNER JOIN TblTotalSale ON TblProduct.[ProductID] = TblTotalSale.[ProductID]
GROUP BY TblProduct.Item, TblProduct.ProductID;

TblStock looks like

StockID ProductID   StockLevel
89  32  200
90  33  72
91  34  72
92  1   528
93  3   528
94  5   528
95  9   528
96  7   528
97  18  80
98  30  72
99  31  204

Product Table looks like

ProductID   Item    Price   StockDelivery   PriceSmall  Large   Small
1   Carling £2.50   528 £1.40   2   1
3   Carlsburg   £2.70   528 £1.60   2   1
5   IPA £2.30   528 £1.20   2   1
7   StrongBow   £2.80   528 £1.65   2   1
9   RevJames    £2.45   528 £1.30   2   1
11  Becks   £2.90   72      1   
12  WKDBlue £2.80   72      1   
13  WKDRed  £2.80   72      1   
14  SmirnoffIce £2.80   72      1   
15  KoppaburgPear   £3.10   72      1   
16  KoppaburgSum    £3.10   72      1   
17  Bulmers £2.90   72      1   
18  Vodka   £1.60   80      1   
19  Gin £1.40   80      1   
20  Sherry  £1.40   80      1   
21  Sambuca £1.70   80      1   
22  Rum £1.60   80      1   
23  Port    £1.60   80      1   
24  Whiskey £1.60   80      1   
25  Baileys £1.60   80      1   
26  Jagermeister    £1.50   80      1   
27  Martini £1.60   80      1   
28  CokeCan £0.85   72      1   
29  Coke    £1.30   204 £0.30   2   1
30  LemonadeCan £0.85   72      1   
31  Lemonade    £1.30   204 £0.30   2   1
32  Squash  £0.25   200     1   
33  Tonic   £0.85   72      1   
34  RedBull £1.90   72      1   
35  Nuts    £0.60   70      1   
36  Crisps  £0.60   70      1

tbltotalSale looks like

TotalSalesID    ProductID   SalePrice   Day Time    Size
370 1   £2.50   05/02/2012  19:53:14    2
371 1   £1.40   05/02/2012  19:53:14    1
372 1   £2.50   05/02/2012  19:53:14    2
373 1   £1.40   05/02/2012  19:53:14    1
374 1   £2.50   05/02/2012  20:25:12    2
375 1   £1.40   05/02/2012  20:25:12    1
376 1   £2.50   05/02/2012  20:25:12    2
377 1   £1.40   05/02/2012  20:25:12    1
378 1   £2.50   05/02/2012  20:25:12    2
379 1   £2.50   05/02/2012  20:25:12    2
380 1   £1.40   05/02/2012  20:25:12    1
381 5   £2.30   05/02/2012  20:25:12    2
382 5   £2.30   05/02/2012  20:25:12    2
383 5   £1.20   05/02/2012  20:25:12    1
384 7   £2.80   05/02/2012  20:25:12    2
385 7   £1.65   05/02/2012  20:25:12    1
386 7   £1.65   05/02/2012  20:25:12    1
387 9   £1.30   05/02/2012  20:25:12    1
435 11  £2.90   05/02/2012  20:25:12    1
436 11  £2.90   05/02/2012  20:42:49    1
437 11  £2.90   05/02/2012  20:42:49    1

I can upload my database if that would be easyer.

have tried to use the following Query for what i want, but it returns 11 results for each product id and none that are correct....

SELECT QrySaleTot.Item, QrySaleTot.ProductID, [QryStockLevel].[Stock]-[QrySaleTot].[Quantity] AS StockOnHand
FROM QrySaleTot, QryStockLevel
GROUP BY QrySaleTot.Item, QrySaleTot.ProductID, [QryStockLevel].[Stock]-[QrySaleTot].[Quantity];

Thanks

Upvotes: 2

Views: 448

Answers (2)

HansUp
HansUp

Reputation: 97101

You included this query in the update to your question:

SELECT
    QrySaleTot.Item,
    QrySaleTot.ProductID,
    [QryStockLevel].[Stock]-[QrySaleTot].[Quantity] AS StockOnHand
FROM QrySaleTot, QryStockLevel
GROUP BY
    QrySaleTot.Item,
    QrySaleTot.ProductID,
    [QryStockLevel].[Stock]-[QrySaleTot].[Quantity];

The first problem is you don't have a join condition ... so each row from QrySaleTot will be matched up with every row from QryStockLevel. That will produce what is called a Cartesian product, or cross join. Revise it to use a join on a field the 2 queries have in common.

The GROUP BY doesn't seem useful here because you're not computing any aggregate values.

Finally, Item is a reserved word. If you must keep that field name, bracket it everywhere you reference it in your queries like this: QrySaleTot.[Item]

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91336

You can join the two tables by id and just subtract.

SELECT Sales.ID, Stock.Level - Sales.Quantity 
FROM Sales 
INNER JOIN Stock
ON Sales.ID = Stock.ID

Updating is not so different. Play around with the query design window. You may wish to read:

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

Upvotes: 2

Related Questions