Solved

Posted on 2009-04-22
303 Views
Help!?
I have a database that has orders (inventory going out) and purchases (inventory coming in). I need a query to comine the two to easily view present inventory.
There is a stockpile table that lists all the products and what how many we should keep in stock.
There is a purchase details query that shows what was purchased (this is actual inventory in stock.)
There is a order details query that shows what was sold (distributed from stockpile).
I need a query that will show what is in inventory for every item (sum purchase details) less order details query (what was sold). Then I need to compare it to the required stockpile to see if we are over/under.

By example Query should function:                                                               Need to purchase
Product             Stockpile    Purchase total    Sales(orders)      Difference     Stockpile - Diff
White mug            10               20                         11                      9                       1
Blue mug              40               40                         4                       36                      4

By example Query should show:
Product               Stockpile           On hand        Order
White mug             10                        9                  1
Blue mug               40                       36                 4

Thanks!
Beth
0
Question by:ba_trainer

LVL 3

Expert Comment

You can use union all function to combine two tables into one and whe select sum
second table should be with negative values of quanities so it will sum correctly
so, create a view name it all_items:
select product ,[Purchase total] as qty from purchases union all select product, -[total (or whatever)] as qty from orders

and when you can group by procut and sum by qty
like:
select product, sum(qty) from all_items

i hope you've got the idea
0

LVL 3

Expert Comment

sorry, spelling mistakes,
You can use union all function to combine two tables into one and when select sum
second table should be with negative values of quantities so it will sum correctly
so, create a view and name it all_items:
select product ,[Purchase total] as qty from purchases union all select product, -[total (or whatever)] as qty from orders

and when you can group by product and sum by qty
like:
select product, sum(qty) from all_items GROUP BY product

you can add extra fields in both tables if you want.

i hope you've got the idea
0

LVL 15

Accepted Solution

Product Name      purchase_sum.SumOfQuantity      orders_sum.SumOfQuantity      Stockpile      to_order
Red Mug      20      4      20      4
Green Mug      20      10      20      10
Black Mug      90      2      50      -38
Gold Mug      20      7      20      7
White Mug      10      4      10      4
US Flag Mug      10      8      10      8

i made 2 simple group by / sum queries....  for orders and products... then linked them to stockpile in a 3rd query..... which does stockpile -  purchse sum + order sum.... to give the above.
0

LVL 3

Expert Comment

sorry, for example, if you use union all and you want to include number of orders and number of purchases
you can do :
select product,[something] as qty, 0 as n_orders, [something] as n_purchases from purchases union all
select product,[something] as qty, [something] as n_orders, 0 as n_purchases from from orders

and when select product, sum[qty], sum[n_orders], sum[n_purchases] from all-items
0

Author Closing Comment

OMG! That's exactly right!! Thank you so much!!
0

Author Comment

Oh no....it didn't count the Silver mug. There are 10 purchased, but none sold.
Did it eliminate because none were sold!? Yikes, this is a problem.
I need the entire inventory...
0

LVL 15

Expert Comment

ok.

so i changed the query so both the summing parts had left joins to stockpile... and said that if they were null ( had no records in purchases or orders, display 0... and then in the totals field, used the updated amount..

here is the new sql for the query

``````SELECT Stockpile.[Product Name], IIf(IsNull([purchase_sum]![SumOfQuantity]),0,[purchase_sum]![SumOfQuantity]) AS purchases, IIf(IsNull([orders_sum]![SumOfQuantity]),0,[orders_sum]![SumOfQuantity]) AS orders, Stockpile.Stockpile, [stockpile]-[purchases]+[orders] AS to_order

FROM purchase_sum RIGHT JOIN (Stockpile LEFT JOIN orders_sum ON Stockpile.[Product Name] = orders_sum.ProductName) ON purchase_sum.ProductName = Stockpile.[Product Name];
``````
0

Author Comment

Yes, that's it! Thanks again! You've been a blessing!!
0

LVL 15

Expert Comment

no problem.
do you understand what i have done? do you need any explanation so you could amend it in future if necessary?
0

## Featured Post

### Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…