SQL JOIN Problem 1

[jrl237]

I'm trying to create a report that shows sales totals by month for each item. I've created a query that pulls all of the item info that needs to be on the report. I've written three queries that pull the monthly sales info from the DB. Now I'd like to join these totals to the original query. Heres the SQL I'm trying:

SELECT DISTINCT view_Items.ItemID, view_Items.Notes, fqryJuly.July AS July, fqryAugust.August AS August, fqrySeptember.September AS September
FROM view_Items
LEFT JOIN fqryJuly ON fqryJuly.ItemID=view_Items.ItemID
LEFT JOIN fqryAugust ON fqryAugust.ItemID=view_Items.ItemID
LEFT JOIN fqrySeptember ON fqrySeptember.ItemID=view_Items.ItemID

This query works fine as long as I only join July. As soon as I add another month, Access complains about a missing operator in the query expression. Am I missing something? Is my syntax incorrect? Can Access not do this? Is there a better way?

Any help would be greatly appreciated.

JRL

 

[Golom]

Syntatically you're a little messed up. The usual way of setting something like this up is (I'll use shorter table names just to make it clearer)

A LEFT JOIN (B LEFT JOIN ( C LEFT JOIN D ON D.X=A.X ) ON C.X = A.X) ON B.X = A.X

However, I suspect that there's a way to do it with a single query. I'll take a guess on the table that underlies fqryJuly, fqryAugust, ...

Select A.ItemID, A.Notes,
(SELECT Sum(Sales) FROM tblSales
WHERE SaleDate BETWEEN #07/01/03# AND #07/31/03#) As [July],
(SELECT Sum(Sales) FROM tblSales
WHERE SaleDate BETWEEN #08/01/03# AND #08/31/03#) As [August],
(SELECT Sum(Sales) FROM tblSales
WHERE SaleDate BETWEEN #09/01/03# AND #09/30/03#) As [September]
FROM view_Items As A

There are elaborations that you can make to this that get you around having hard-coded dates in your SQL.

 

[jrl237]

Thanks, Golom. I got the join to work, and I'm going to try out your single query method. It would be great if I could eliminate some queries.

Thanks again for your help.