Query returns empty columns

[Amy]

Hi,
The query below returns sales made in 2007 and 2006 and works fine. However,
if a client didn't have sells in 2006, the query returns empty fields (for
2007 as well).
Can anyone pls tell me why? I think the problem is with the WHERE statement
but cannot see it.
TIA
Amy

SELECT *
FROM (SELECT ROUND(SUM(CASE DATEPART(M, SELL_DATE) WHEN 1 THEN
PRICE ELSE 0 END), 0) AS Jan,
ROUND(SUM(PRICE), 0) AS TOT07,
CLIENT_ID, COUNT(PRICE) AS ORDERS07
FROM dbo.SELLS
WHERE (DATEPART(YYYY, CONVERT(DATETIME,
SELL_DATE))
= DATEPART(YYYY, GETDATE()))
GROUP BY CLIENT_ID) AS A INNER JOIN
(SELECT ROUND(SUM(PRICE), 0) AS TOT06,
COUNT(PRICE) AS ORDERS06, CLIENT_ID
FROM dbo.SELLS AS SELLS_1
WHERE (DATEPART(YYYY, CONVERT(DATETIME,
SELL_DATE)) = 2006)
GROUP BY CLIENT_ID) AS B ON A.CLIENT_ID =
B.CLIENT_ID
WHERE (A.CLIENT_ID = 100)

///The date format is yyyymmdd thus the datetime convertion
Access03, sql05

 

[Guest]

Seems like that query could be a lot simpler....

Anyway change "INNER JOIN" to "LEFT JOIN". If that doesn't work, try "RIGHT
JOIN" instead.

You may run into a problem where there are clients with sales in 2006 but
none in 2007. In that case you need a full outer join which Access does not
support, or a UNION query which joins all the possibilities together.

 

[Guest]

Try this query --
SELECT SELLS.CLIENT_ID, Sum(IIf(Format([SELL_DATE],"yyyy")="2007",1,0)) AS
ORDERS07, Round(Sum(IIf(Format([SELL_DATE],"yyyy")="2007",[PRICE],0)),0) AS
TOT07, Sum(IIf(Format([SELL_DATE],"yyyy")="2006",1,0)) AS ORDERS06,
Round(Sum(IIf(Format([SELL_DATE],"yyyy")="2006",[PRICE],0)),0) AS TOT06
FROM SELLS
GROUP BY SELLS.CLIENT_ID
ORDER BY SELLS.CLIENT_ID;