Query: Data not indexing when null

  • Thread starter Thread starter Jason Trolian
  • Start date Start date
J

Jason Trolian

I work in a collection firm. I have built numerous querys
that gather data for me and I display the data in 1
report. One section of this shows the amount of checks we
have in house not awaiting to be posted ("Back end")
query1 gathers all the transactions for each collector
including all details (ie: Check#, Amount, Commision amt.,
etc)

Query2 totals the transactions and lists them for
reporting seperated by collector name.

The problem is, when there is no money in the back end for
a specific collector during the queried date range, the
collectors name wont list with a zero. It simply ignores
the null value and moves on. When I finally run the
report, even though the collector has other values from
different querys, the report will not list the collector.

I believe that I need to use "if is null" statment but not
sure in what context.

Thanks in advance for any help you can offer.
Jason Trolian
 
I work in a collection firm. I have built numerous querys
that gather data for me and I display the data in 1
report. One section of this shows the amount of checks we
have in house not awaiting to be posted ("Back end")
query1 gathers all the transactions for each collector
including all details (ie: Check#, Amount, Commision amt.,
etc)

Query2 totals the transactions and lists them for
reporting seperated by collector name.

The problem is, when there is no money in the back end for
a specific collector during the queried date range, the
collectors name wont list with a zero. It simply ignores
the null value and moves on. When I finally run the
report, even though the collector has other values from
different querys, the report will not list the collector.

I believe that I need to use "if is null" statment but not
sure in what context.
Click to expand...

I think you may need an Outer Join, but not knowing anything about the
structure of your tables or your queries it's impossible to say. Could
you post the SQL view of your query?
 
query1 - (back end)

SELECT DISTINCTROW COLCHECK.acctno, COLCHECK.dbtrno,
COLCHECK.trxamt, COLCHECK.chkdate, COLCLNT.clntno,
COLCHECK.collno, COLCLNT.com_name, COLCOMM.rate_1,
([TRXAMT]*[RATE_1]/100) AS Expr1, COLCLNT.name,
COLCOL.collnam
FROM (((COLCHECK INNER JOIN COLACCT ON COLCHECK.acctno =
COLACCT.acctno) INNER JOIN COLCLNT ON COLACCT.aclntno =
COLCLNT.clntno) INNER JOIN COLCOMM ON COLCLNT.com_name =
COLCOMM.com_name) INNER JOIN COLCOL ON COLACCT.collno =
COLCOL.collno
WHERE (((COLCHECK.chkdate) Between #10/1/2003# And
#10/31/2003#));

query2 (Bck End Summary)

SELECT DISTINCTROW [back end].collno, colcol.collnam, Sum
([back end].trxamt) AS [gross back end current], Sum(Round
([Expr1],2)) AS [net back end]
FROM [back end] INNER JOIN colcol ON [back end].collno =
colcol.collno
GROUP BY [back end].collno, colcol.collnam, colcol.mtdcoll;


query2
 
query1 - (back end)
Click to expand...

Queries should generally exist only in the frontend; any particular
reason why this is in the backend?
SELECT DISTINCTROW COLCHECK.acctno, COLCHECK.dbtrno,
COLCHECK.trxamt, COLCHECK.chkdate, COLCLNT.clntno,
COLCHECK.collno, COLCLNT.com_name, COLCOMM.rate_1,
([TRXAMT]*[RATE_1]/100) AS Expr1, COLCLNT.name,
COLCOL.collnam
FROM (((COLCHECK INNER JOIN COLACCT ON COLCHECK.acctno =
COLACCT.acctno) INNER JOIN COLCLNT ON COLACCT.aclntno =
COLCLNT.clntno) INNER JOIN COLCOMM ON COLCLNT.com_name =
COLCOMM.com_name) INNER JOIN COLCOL ON COLACCT.collno =
COLCOL.collno
WHERE (((COLCHECK.chkdate) Between #10/1/2003# And
#10/31/2003#));
Click to expand...

I take it that COLCOL.collnam is the name of the collector? How are
the tables related - what's on the "one" side, what's on the "many"?
With the inner joins in this query you will only get a value of
collnam when matching records exist in all four tables. It's not an
issue of there being a zero amount, or a null amount - with Inner Join
there simply *is no record there*. Changing the last INNER JOIN to a
Right Outer Join will return all records in COLCOL whether or not
there is data in the other tables, but I'm not quite sure if that's
what you actually want.
query2 (Bck End Summary)

SELECT DISTINCTROW [back end].collno, colcol.collnam, Sum
([back end].trxamt) AS [gross back end current], Sum(Round
([Expr1],2)) AS [net back end]
FROM [back end] INNER JOIN colcol ON [back end].collno =
colcol.collno
GROUP BY [back end].collno, colcol.collnam, colcol.mtdcoll;
Click to expand...

Curious: ColCol is part of the [back end] query, and now you're
including it in this query a second time. Why? Maybe you need the
Outer Join on this query instead.
 
Back
Top