Sum/Product in Query

[atisz]

Hi,

I have to calculate the quantity of the ordered products, grouped by Product_ID, and I have to be able to print this totals for specific dates.

For this query I need data from 3 tables:

tblProducts
Product_Name

tblOrders
Product_ID
Ordered_Quantity

tblCustomers
Delivery_Date
Products_Ready (Yes/No type, used for showing only those records which I need, in this case I will set it in my query to "No")

Whithout including in query Delivery_Date, everithing it's simple. I made a query, grouped by Product_ID, and running a sum on Ordered_Quantity.

But if I include Delivery_Date, because it can be different from order to order, the query doesn't show the totals the right way, once/Product_ID.

Can anyone help me on this? A piece of SQL code or everithing else is greatly appreciatde.

Thank you in advance, Attila

 

[ajetrumpet]

I have to calculate the quantity of the ordered products, grouped by Product_ID, and I have to be able to print this totals for specific dates.

Attila,

You will have to make two queries for this, because any field that is included in a query that is not calculated must be a part of the grouping. The furthest you can get with one query is to see the total amount of products ordered on each delivery date, grouped by ProductID. But, is that possibly what you are looking for? If so, the query would be written like this...

SELECT product, deliverdate, Sum(OrderedQuantity) As NewField

     FROM table INNER JOIN othertable ON (field = field INNER JOIN othertable2
     ON ())

That is just an example of how the SQL will look if you are pulling from 3 tables. It is not the correct syntax for it, but similar in nature. Querying with the built-in grid will do all that for you though.

Additional Reference

http://www.access-programmers.co.uk/forums/showthread.php?t=135763

 

[atisz]

???????

ajetrumpet, you wrote:

You will have to make two queries for this, because any field that is included in a query that is not calculated must be a part of the grouping.

As you are telling me, one query is not enough to solve the problem, I need two. I really don't care how many are necessary, all I know is that I have to make it work somehow, because all it's about this in my database, and I have tried to work around the problem, but no results since now.
Can you be more specific regarding the 2 query's solution, or could anyone else suggest me a solution for this sum issue?

Thanks, Attila

 

[ajetrumpet]

I might be confused by what you're asking. Try any one of these. If they aren't what you need, you're welcome to tell me what else I can do...

SELECT product, deliverydate, Sum(OrderedQuantity) As NewField

     FROM table INNER JOIN othertable ON (field = field INNER JOIN othertable2
     ON ())

GROUP BY product, deliverydate

SELECT deliverydate, Sum(OrderedQuantity) As NewField

     FROM table INNER JOIN othertable ON (field = field INNER JOIN othertable2
     ON ())

GROUP BY deliverydate