Query returns empty columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amy - 26 Oct 2007 19:23 GMT

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

Reply to this Message

Jerry Whittle - 26 Oct 2007 20:05 GMT

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.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hi,
> The query below returns sales made in 2007 and 2006 and works fine. However,
[quoted text clipped - 26 lines]
> ///The date format is yyyymmdd thus the datetime convertion
> Access03, sql05

Reply to this Message

KARL DEWEY - 26 Oct 2007 20:09 GMT

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;

Signature

KARL DEWEY
Build a little - Test a little

> Seems like that query could be a lot simpler....
>
[quoted text clipped - 35 lines]
> > ///The date format is yyyymmdd thus the datetime convertion
> > Access03, sql05

Reply to this Message