Need help with query that contain Nulls

[JVZ]

Hello all,

I need some help in writing a query.

What I want is a query that will return a result set that look like:

Date cabs LDPP
1/14/2003 1252 0
1/15/2003 1033 2
1/16/2003 1235 0
....

The problem that I'm having is that the table that contains that data for the column LDPP does not have all the dates as the table that contains the data from CABS...so I get dates that are missing in my result set. I would like the result to contain all the date from both tables with there values summed up...i.e.: one table contains data for 1/1/2003, and the other table does not...I would like the for 1/1/2003 from table A to be returned and maybe a value of 0 for table B on the other side, as a place holder....can this be done?

Here is a brief layout of the two tables... (There is no way to change the layoff of the tables since they are link from other DB Servers...)

tblCabs
-------
CycleDate
CIC
Lucent1_Comp
...

tblLDPP
-------
ActivityDate
Switch
CIC
CompletedCalls
...

*** This is what I have - However it just not producing the results that I want ***

SELECT tblCabs.CycleDate, Sum(tblCabs.[LUCENT1_Comp]) AS Cabs, tblLDPP.CIC, Sum(NZ([CompletedCalls])) AS LDPP
FROM tbl_Cabs, tbl_LDPP
WHERE (((tblCabs.Date) Between #1/1/2003# And #1/31/2003#) AND ((tblLDPP.Switch)="lucent1") AND ((tblLDPP.CIC)="6963") AND ((tblCabs.CIC)="6963"))
GROUP BY tblCabs.CycleDate, tblLDPP.CIC;

Any ideas?

 

[dodgychris]

SELECT tblCabs.CycleDate, Sum(tblCabs.[LUCENT1_Comp]) AS Cabs, tblLDPP.CIC, Sum(NZ([CompletedCalls])) AS LDPP
FROM tbl_Cabs left join tbl_LDPP on (tblLDPP.CIC= tblCabs.CIC)
WHERE (((tblCabs.Date) Between #1/1/2003# And #1/31/2003#) AND ((tblLDPP.Switch)="lucent1&quot
GROUP BY tblCabs.CycleDate, tblLDPP.CIC

This might do the job for you, it's an outer join, so in this case (a 'left'join) every record from the table on the left (tbl_cabs) is shown, but only the records from the table on the right are shown where they match...

Hope this helps,

Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda