HOW?: Replace Non-Existant with Zero

  • Thread starter Thread starter Kosta
  • Start date Start date
K

Kosta

Two Tables:
Accounts, Income

The problem:
How do I report on all the Accounts on a certain time period
whether they have a corresponding record under income or not?

Here is one of the countless (!) queries I have tried.

SELECT
A.AccountID,
Sum(NZ(I.CurNet,0))
From
Accounts AS [A] LEFT OUTER JOIN
Income AS ON A.AccountID = I.AccountID
WHERE
I.BrDate = CDate('01/07/2003') OR
I.BrDate IS NULL
GROUP BY
A.AccountID;



The request:
PLEASE HEEEEEEEEEEEEEEEEEELP!
 
Dear dsk:

So, when you run the query in your example, what's wrong? Does it
give you an error message, or does it not have the result you wanted?
If the latter, does it omit data you wanted? If so, what data is
there that was omitted? Or does it include data you didn't want?

Just on the off chance I might catch something without realizing it,
could you try this:

SELECT A.AccountID, Sum(Nz(I.CurNet,0))
FROM Accounts A
LEFT JOIN Income I ON A.AccountID = I.AccountID
WHERE Nz(I.BrDate, #1/7/2003#) = #01/07/2003#
GROUP BY A.AccountID;


Two Tables:
Accounts, Income

The problem:
How do I report on all the Accounts on a certain time period
whether they have a corresponding record under income or not?

Here is one of the countless (!) queries I have tried.

SELECT
A.AccountID,
Sum(NZ(I.CurNet,0))
From
Accounts AS [A] LEFT OUTER JOIN
Income AS ON A.AccountID = I.AccountID
WHERE
I.BrDate = CDate('01/07/2003') OR
I.BrDate IS NULL
GROUP BY
A.AccountID;



The request:
PLEASE HEEEEEEEEEEEEEEEEEELP!
Click to expand...


Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top