Join Problems

[Tonk]

The qry COADS contains only 86 records, one for each county. The
qryScanLog contains 88 records, one for each county. I have tried all
possible joins and I cannot get 88 records to show up in the resulting
query - I get only 86. Under the join properties I specify that I want
all records in qryScanLog and those that match in qryCOADS, yet still
only end up with the 86 records in qryCOADs.

Would someone be willing to telling me what I'm doing wrong?

Thanks so much.

Mike

SELECT qryCOADS.County, qryCOADS.Records AS COADS, qryScanLog.Records
AS Harvest
FROM qryCOADS LEFT JOIN qryScanLog ON qryCOADS.County =
qryScanLog.COUNTY
WHERE (((qryCOADS.ScanGroup)="EA") AND ((qryScanLog.ScanGroup)="EA"));

 

[giorgio rancati]

Hi Tonk,

The where condition *AND qryScanLog.ScanGroup="EA"* transforms the Left Join
in Inner Join.

If you want obtain all qryCOADS rows try this query.
----
SELECT qryCOADS.County, qryCOADS.Records AS COADS, qryScanLog.Records
AS Harvest
FROM qryCOADS LEFT JOIN qryScanLog
ON qryCOADS.County = qryScanLog.COUNTY AND (qryScanLog.ScanGroup)="EA"
WHERE qryCOADS.ScanGroup="EA"
----
ps. don't remove the parenthesis (qryScanLog.ScanGroup)="EA"

or try this query
----
SELECT qryCOADS.County, qryCOADS.Records AS COADS, qryScanLog.Records
AS Harvest
FROM qryCOADS LEFT JOIN
(SELECT * FROM qryScanLog WHERE ScanGroup="EA") AS qryScanLog
ON qryCOADS.County = qryScanLog.COUNTY
WHERE qryCOADS.ScanGroup="EA"

 

[Tonk]

Jerry - Thank you for taking time to reply. I will experiment with
your query and let you know how things go.

Thanks again!

Tonk

 

[Tonk]

Hi Giorgio - Thank you for taking time to reply. As I mentioned to
Jerry, I will experiment with your query and let you know how things
go.

Thanks again!

Tonk

Hi Tonk,

The where condition *AND qryScanLog.ScanGroup="EA"* transforms the Left Join
in Inner Join.

If you want obtain all qryCOADS rows try this query.
----
SELECT qryCOADS.County, qryCOADS.Records AS COADS, qryScanLog.Records
AS Harvest
FROM qryCOADS LEFT JOIN qryScanLog
ON qryCOADS.County = qryScanLog.COUNTY AND (qryScanLog.ScanGroup)="EA"
WHERE qryCOADS.ScanGroup="EA"
----
ps. don't remove the parenthesis (qryScanLog.ScanGroup)="EA"

or try this query
----
SELECT qryCOADS.County, qryCOADS.Records AS COADS, qryScanLog.Records
AS Harvest
FROM qryCOADS LEFT JOIN
(SELECT * FROM qryScanLog WHERE ScanGroup="EA") AS qryScanLog
ON qryCOADS.County = qryScanLog.COUNTY
WHERE qryCOADS.ScanGroup="EA"
----

bye
--
Giorgio Rancati
[Office Access MVP]

The qry COADS contains only 86 records, one for each county. The
qryScanLog contains 88 records, one for each county. I have tried all
possible joins and I cannot get 88 records to show up in the resulting
query - I get only 86. Under the join properties I specify that I want
all records in qryScanLog and those that match in qryCOADS, yet still
only end up with the 86 records in qryCOADs.

Would someone be willing to telling me what I'm doing wrong?

Thanks so much.

Mike

SELECT qryCOADS.County, qryCOADS.Records AS COADS, qryScanLog.Records
AS Harvest
FROM qryCOADS LEFT JOIN qryScanLog ON qryCOADS.County =
qryScanLog.COUNTY
WHERE (((qryCOADS.ScanGroup)="EA") AND ((qryScanLog.ScanGroup)="EA"));