Help overcoming NULL in a query

G

Guest

I am trying to build a table from data stored in our SQL server, my problem
is I want all accounts specified in the query to be listed regardless if they
purchased a particular item or not. If they didn't then I want a zero for
that record however I am having trouble accomplishing this I have tried IIF
IsNull and a left join could someone look at this code and see what I maybe
doing wrong. The table Spring2007 is a list of the items I want to lookup the
sales for but if a particular customer did not purchase anything in
Spring2007 I want it to list the item and the customer number (CTM_NBR) as 0

PS: when I started the Left Join was an INNER Join and I did not use the IIF
IsNull

Thanks in advance,

Terry

Code: 
strSQL = "SELECT Spring2007.ITM_NUM, Spring2007.Title, (PROOLN_M.SHP_CTM)
as CUST,
Sum(IIF(IsNull([PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS]),0,[PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS]))
AS UNITS_ORD INTO tblTOP15a " & _         "FROM (((PROOLN_M INNER JOIN
PROORD_M ON PROOLN_M.ORD_NUM = PROORD_M.ORD_NUM) " & _         "INNER JOIN
CDSITM_M ON PROOLN_M.ITM_NUM = CDSITM_M.ITM_NUM) INNER JOIN CDSADR_M ON
PROOLN_M.SHP_CTM = CDSADR_M.CTM_NBR) " & _         "LEFT JOIN Spring2007 ON
CDSITM_M.ITM_NUM = Spring2007.ITM_NUM " & _         "WHERE
(((PROORD_M.ORG_NUM)=' ') AND ((PROOLN_M.QTY_ORD)<>0) AND
((PROOLN_M.ORD_NUM)<'90000000') " & _         "AND ((PROOLN_M.SHP_CTM)IN
('000000604974','000081275734','000000685361','000080328280','000080291972','000000740071','000000704601','000080317502','000080332322','000080028698'
" & _
",'000080219488','000080101798','000080119800','000080479597','000000740394','000080045644','000080089787','000000737063','000000782379','000000748880'
" & _
",'000080382653','000080422536','000080052398','000080119747','000080562294','000080091055','000080119742','000080119785','000000795627','000080119706'
" & _
",'000080091050','000080091058','000080479638','000080065432','000080547860','000000777499','000080029458','000080038894','000080051948','000080097377'
" & _
",'000080119680','000080119681','000080119685','000080119688','000080119700','000080119872','000080120248','000080219492','000080265549','000080282591'
" & _
",'000080291985','000080317502','000080332322','000080332323','000080348610','000080479638','000000777777','000080410980','000080740385','000080740387'
" & _
",'000080791774','000080851104','000080918031','000080965290','000080965292','000070036325','000081203134','000081203135','000070037501','000070046822'
" & _
",'000000601248','000080369130','000000601133','000080960480','000070047796','000000775986','000070048061','000070068299','000070036335'))
" & _         "AND ((CDSADR_M.ADR_CDE)='STANDARD') AND
((CDSADR_M.ADR_FLG)='0')) " & _         "GROUP BY Spring2007.ITM_NUM,
PROOLN_M.SHP_CTM, Spring2007.Title; "
 
M

Michel Walsh

Your query seems already complex, so, I suggest that first, you do it right
for clients having bought something, that implies ONLY inner join. SAVE that
query (view, if using MS SQL Server). Next, make another query, include the
previous saved query AND the table with all the clients name, join these two
with an outer join to keep the list with all the clients name. In the
SELECT clause, use

Nz( savedQueryName.SomeOfItsFieldName, 0 )


if you are using Jet, or


COALESCE( savedQueryName.SomeOfItsFieldName, 0 )


if you are using MS SQL Server.




Hoping it may help,
Vanderghast, Access MVP


tbonehwd said:
I am trying to build a table from data stored in our SQL server, my problem
is I want all accounts specified in the query to be listed regardless if
they
purchased a particular item or not. If they didn't then I want a zero for
that record however I am having trouble accomplishing this I have tried
IIF
IsNull and a left join could someone look at this code and see what I
maybe
doing wrong. The table Spring2007 is a list of the items I want to lookup
the
sales for but if a particular customer did not purchase anything in
Spring2007 I want it to list the item and the customer number (CTM_NBR) as
0

PS: when I started the Left Join was an INNER Join and I did not use the
IIF
IsNull

Thanks in advance,

Terry

Code: 
strSQL = "SELECT Spring2007.ITM_NUM, Spring2007.Title, (PROOLN_M.SHP_CTM)
as CUST,
Sum(IIF(IsNull([PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS]),0,[PROOLN_M.QTY_ORD-PROOLN_M.QTY_LOS]))
AS UNITS_ORD INTO tblTOP15a " & _         "FROM (((PROOLN_M INNER JOIN
PROORD_M ON PROOLN_M.ORD_NUM = PROORD_M.ORD_NUM) " & _         "INNER JOIN
CDSITM_M ON PROOLN_M.ITM_NUM = CDSITM_M.ITM_NUM) INNER JOIN CDSADR_M ON
PROOLN_M.SHP_CTM = CDSADR_M.CTM_NBR) " & _         "LEFT JOIN Spring2007
ON
CDSITM_M.ITM_NUM = Spring2007.ITM_NUM " & _         "WHERE
(((PROORD_M.ORG_NUM)=' ') AND ((PROOLN_M.QTY_ORD)<>0) AND
((PROOLN_M.ORD_NUM)<'90000000') " & _         "AND ((PROOLN_M.SHP_CTM)IN
('000000604974','000081275734','000000685361','000080328280','000080291972','000000740071','000000704601','000080317502','000080332322','000080028698'
" & _
",'000080219488','000080101798','000080119800','000080479597','000000740394','000080045644','000080089787','000000737063','000000782379','000000748880'
" & _
",'000080382653','000080422536','000080052398','000080119747','000080562294','000080091055','000080119742','000080119785','000000795627','000080119706'
" & _
",'000080091050','000080091058','000080479638','000080065432','000080547860','000000777499','000080029458','000080038894','000080051948','000080097377'
" & _
",'000080119680','000080119681','000080119685','000080119688','000080119700','000080119872','000080120248','000080219492','000080265549','000080282591'
" & _
",'000080291985','000080317502','000080332322','000080332323','000080348610','000080479638','000000777777','000080410980','000080740385','000080740387'
" & _
",'000080791774','000080851104','000080918031','000080965290','000080965292','000070036325','000081203134','000081203135','000070037501','000070046822'
" & _
",'000000601248','000080369130','000000601133','000080960480','000070047796','000000775986','000070048061','000070068299','000070036335'))
" & _         "AND ((CDSADR_M.ADR_CDE)='STANDARD') AND
((CDSADR_M.ADR_FLG)='0')) " & _         "GROUP BY Spring2007.ITM_NUM,
PROOLN_M.SHP_CTM, Spring2007.Title; "
Click to expand...
 
Top