Sum Function

[kbreiss]

I have a query that does a recap on inventory for the time frame that the user enters. Basically it says how much was ordered, received and distributed and Qnty on Hand for the specified time frame. If you notice in my query I say "Sum(UNITS_RECEIVED)-Sum(Units_Distributed) AS QNTY_ON_HAND". The Qnty on Hand is only for that time frame. I'm wondering how I can do this so that it will give me a true Qnty on Hand by summing all Units_Received and substracting all Units_Distributed....regardless of the time fram entered.

SELECT PRODUCTS.PROD_ID, PRODUCTS.PROD_NAME, Sum(INV_TRANSACTIONS.UNITS_ORDERED) AS ORDERED, Sum(INV_TRANSACTIONS.UNITS_RECEIVED) AS RECEIVED, Sum(INV_TRANSACTIONS.UNITS_DISTRIBUTED) AS DISTRIBUTED, Sum(UNITS_RECEIVED)-Sum(Units_Distributed) AS QNTY_ON_HAND, PRODUCTS.REORDER_LEVEL, tbl_Shipping.ShippingMethod, INV_TRANSACTIONS.DATE
FROM tbl_Shipping INNER JOIN (PRODUCTS INNER JOIN INV_TRANSACTIONS ON PRODUCTS.PROD_ID = INV_TRANSACTIONS.PROD_ID) ON tbl_Shipping.Shipping_ID = PRODUCTS.SHIPPING_ID
WHERE (((INV_TRANSACTIONS.DATE)>=[forms]![Report Date Range]![BeginDate] And (INV_TRANSACTIONS.DATE)<=[forms]![Report Date Range]![EndDate]))
GROUP BY PRODUCTS.PROD_ID, PRODUCTS.PROD_NAME, PRODUCTS.REORDER_LEVEL, tbl_Shipping.ShippingMethod, INV_TRANSACTIONS.DATE;

Thanks in advance,
Kacy
________
BODY SCIENCE

 

[FoFa]

Use a subquery for the column

 

[kbreiss]

Thanks for replying.....I looked in the help section and still having trouble figuring out how to use the subquery. Can you please help?

Thanks,
Kacy
________
Zx14 vs hayabusa

 

[kbreiss]

I must be brain dead b/c I'm not following.... I'm not quite for sure if I explained myself clearly either. Here's my goal....
I want to find the number of units ordered, received, distributed for a time frame that the user enters. I then want to sum the total received minus the total distributed disregarding the time frame. So example, for May we ordered 20 units, received 20 units and distributed 10. The Qnty on Hand shouldn't reflect these numbers but be an actual total of received minus distributed regardless of time frame.

Thanks,
Kacy
________
Anal Black

 

[kbreiss]

ok, your gonna kill me.......I've got the two queries written. Seperately they return what I want. However, not finding out how I can join these recordsets. Please help, I'm getting closer.

Thanks,
Kacy
________
Medical marijuana

 

[kbreiss]

Thanks Pat,
Got it working fine. I have three different queries b/c they are different sums, that I joined together by a fourth query and use the fourth query as my recordsource of my report. Not for sure if this is bad practice (the number of queries it takes), but it's working. Thanks a ton,

Kacy
________
Airsoft electric