Join Problems

T

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"));
 
G

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"
 
T

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
 
T

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
giorgio said:
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]

Tonk said:
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"));
Click to expand...
Click to expand...
 
Top