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

 

[Pat Hartman]

Use just an end date for your selection criteria rather than a range. This will give you numbers at a point in time.

 

[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

 

[Pat Hartman]

Since you need two separate sets of data; the units ordered, received, delivered during a time period plus the units on hand, you need two separate queries to sum the data. The first query needs the date range as you have it. The second query needs only an end date. You can then join these recordsets to obtain your desired result.

 

[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

 

[Pat Hartman]

How you proceed from here depends on where you're going. Are you trying to make a report? Use a main report with a subreport. Base the main report on the on-hand query and the subreport on the other query. The same technique would be used for a form.

To make a single recordset you would either use a union or a join, again depending on what your objective was.

 

[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