left join not returning enough records

[shadowsong]

I would like to generate a list of dealers, with date of last claim
for those who have submitted any, and either N/A or null for those who
have not submitted valid entries.

There are 341 records in my dealer table, but the query only returns
the 227 who have submitted claims, not any of the others. I assumed
originally that it was because I was using an inner join when I should
have been using a left join, but either join type returns the same
number of records.

SELECT tblAWSC.DLRNUM, tblAWSC.DLRNAME, Max([All Claims].DATE) AS
[MOST RECENT]
FROM tblAWSC LEFT JOIN [All Claims] ON tblAWSC.DLRNUM = [All
Claims].DLRNUM
WHERE (([All Claims].DLRNUM=[tblAWSC].[DLRNUM])
AND ([All Claims].APPROVED<>"N")
AND ([All Claims].TOTAL<>0))
GROUP BY tblAWSC.DLRNUM, tblAWSC.DLRNAME;


What should I do to make it return all records from the dealer table?

 

[John Spencer]

The problem is you are applying criteria to the table (all claims) which is
on the right side of the left join. Doing so negates the left join,
especially since you have
WHERE [All Claims].DLRNUM=[tblAWSC].[DLRNUM]
as part of the query criteria.

Since your table name contains spaces you will need to do this in two
queries - query one to get those were there are claims and query two using
that and the tblAwsc in a left join query.

For example: qClaimsNotApproved
SELECT DLRNUM, Max([All Claims].[DATE]) AS MostRecent
FROM [All Claims]
WHERE [All Claims].APPROVED<>"N"
AND [All Claims].TOTAL<>0
GROUP BY DLRNUM


SELECT tblAWSC.DLRNUM
, tblAWSC.DLRNAME
, qClaimsNotApproved.MostRecent
FROM tblAWSC LEFT JOIN qClaimsNotApproved
ON tblAWSC.DLRNUM = qClaimsNotApproved.Claims.DLRNUM

OR you could do this with a correlated subquery

SELECT DLRNUM, DLRNAME
, (SELECT Max([Date])
FROM [ALL CLAIMS] as A
WHERE A.DLRNUM = tblAWSC.DLRNUM
AND A.APPROVED<>"N"
AND [A].TOTAL<>0) as MostRecent
FROM tblAWSC



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Maurice]

Based on the group by it could be that some records are grouped. Try running
it without the group by to see if you do get the requested output then.

 

[shadowsong]

The problem is you are applying criteria to the table (all claims) which is
on the right side of the left join. Doing so negates the left join,
especially since you have
WHERE [All Claims].DLRNUM=[tblAWSC].[DLRNUM]
as part of the query criteria.

Since your table name contains spaces you will need to do this in two
queries - query one to get those were there are claims and query two using
that and the tblAwsc in a left join query.

For example: qClaimsNotApproved
SELECT DLRNUM, Max([All Claims].[DATE]) AS MostRecent
FROM [All Claims]
WHERE [All Claims].APPROVED<>"N"
AND [All Claims].TOTAL<>0
GROUP BY DLRNUM

SELECT tblAWSC.DLRNUM
, tblAWSC.DLRNAME
, qClaimsNotApproved.MostRecent
FROM tblAWSC LEFT JOIN qClaimsNotApproved
ON tblAWSC.DLRNUM = qClaimsNotApproved.Claims.DLRNUM

OR you could do this with a correlated subquery

SELECT DLRNUM, DLRNAME
, (SELECT Max([Date])
FROM [ALL CLAIMS] as A
WHERE A.DLRNUM = tblAWSC.DLRNUM
AND A.APPROVED<>"N"
AND [A].TOTAL<>0) as MostRecent
FROM tblAWSC

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I would like to generate a list of dealers, with date of last claim
for those who have submitted any, and either N/A or null for those who
have not submitted valid entries.

There are 341 records in my dealer table, but the query only returns
the 227 who have submitted claims, not any of the others. I assumed
originally that it was because I was using an inner join when I should
have been using a left join, but either join type returns the same
number of records.

SELECT tblAWSC.DLRNUM, tblAWSC.DLRNAME, Max([All Claims].DATE) AS
[MOST RECENT]
FROM tblAWSC LEFT JOIN [All Claims] ON tblAWSC.DLRNUM = [All
Claims].DLRNUM
WHERE (([All Claims].DLRNUM=[tblAWSC].[DLRNUM])
AND ([All Claims].APPROVED<>"N")
AND ([All Claims].TOTAL<>0))
GROUP BY tblAWSC.DLRNUM, tblAWSC.DLRNAME;

What should I do to make it return all records from the dealer table?

The correlated subquery did the trick, thank you.