• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Create query access to calculate stockpile

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
ba_trainer
Asked:
ba_trainer
  • 3
  • 3
  • 3
1 Solution
 
DRY_GINCommented:
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
 
DRY_GINCommented:
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
 
Simon BallCommented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
DRY_GINCommented:
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
 
ba_trainerAuthor Commented:
OMG! That's exactly right!! Thank you so much!!
0
 
ba_trainerAuthor Commented:
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
 
Simon BallCommented:
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];

Open in new window

0
 
ba_trainerAuthor Commented:
Yes, that's it! Thanks again! You've been a blessing!!
0
 
Simon BallCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now