Multiple Table Query

[Strongbow1980]

I have a few tables, but need to get data from 3 of them in 1 query. The Tables are CostCentre, OrderItems and OrderHeader

I need to run a query that will total the cost of all items purchased between certain dates for each cost centre. I can do this for all orders in the database using this query:

SELECT CostCentres.Description, Sum(OrderItems.Price) From CostCentres, OrderItems WHERE OrderItems.CostCentre = CostCentre.Code GROUP BY CostCentres.Description

But how can I limit the records to only include ones from a certain month, without grouping them by date as well?

 

[traingamer]

Just add the date criteria to the WHERE clause.

 WHERE OrderItems.CostCentre = CostCentre.Code AND [b]date criteria here[/b]
GROUP BY CostCentres.Description

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Strongbow1980]

I tried that by including the OrderHead.OrderDate field and OrderHead table in the SELECT statement, and adding WHERE OrderHead.PONumber = OrderItems.PONumber to join the tables, and then the date criteria. but get an error saying something along the lines of OrderHead.OrderDate not being included in an aggregate function (as I don't want the records grouped by date)

I could solve this by holding the date against each OrderItem record, but as each order can only have one date, I'd rather keep it in the OrderHead table.

 

[PHV]

Use the OrderDate only in the WHERE clause, ie nor in the SELECT list nor in the HAVING clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Strongbow1980]

Thanks, I have changed the statement to this, but now get an Expected Paramaters:2 error - Is this something to do with the dates? I have tried it both with the dates enclosed in '#' characters and without.

SELECT CostCentres.Description, Sum(OrderItems.Price) As TotalSpend FROM CostCentres, OrderItems WHERE (CostCentres.Code = OrderItems.CostCentre) AND (OrderHead.PONumber = OrderItems.PONumber) AND (OrderHead.OrderDate BETWEEN 01/05/2005 AND 31/05/2005) GROUP BY CostCentres.Description ORDER BY Sum(OrderItems.Price) DESC

 

[PHV]

What about this ?
SELECT CostCentres.Description, Sum(OrderItems.Price) As TotalSpend
FROM (CostCentres
INNER JOIN OrderItems ON CostCentres.Code = OrderItems.CostCentre)
INNER JOIN OrderHead ON OrderItems.PONumber = OrderHead.PONumber
WHERE OrderHead.OrderDate BETWEEN #2005-05-01# AND #2005-05-31#
GROUP BY CostCentres.Description
ORDER BY Sum(OrderItems.Price) DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244