Join Yields Unexplainable Results

[Tonk]

Dear NG,

I'm very new at queries, and as you'll see here in a second - my
queries show it! I've created 3 queries to do what I feel certain can
be accomplished with a single query. I've beat my head against the
wall trying to figure out how to get the results I need with a single
query of two tables, but I'm no where close.

These are the 3 queries:

Query1
SELECT County.COUNTY, tblCheckOffAndDispostionSheet.CheckCounty,
Sum(tblCheckOffAndDispostionSheet.Quantity) AS DeliveredForms
FROM tblCheckOffAndDispostionSheet INNER JOIN County ON
tblCheckOffAndDispostionSheet.CheckCounty = County.CountyID
WHERE (((tblCheckOffAndDispostionSheet.ScanGroup)="YG"))
GROUP BY County.COUNTY, tblCheckOffAndDispostionSheet.CheckCounty;


Query2
SELECT County.COUNTY, OLDTEST.BatchTrackID AS CheckCounty,
Count(OLDTEST.BatchTrackID) AS ScannedForms
FROM County INNER JOIN OLDTEST ON County.CountyID =
OLDTEST.BatchTrackID
WHERE (((OLDTEST.ScanGroup)="YG") AND ((OLDTEST.BatchTrackID) Is Not
Null And (OLDTEST.BatchTrackID)<=88))
GROUP BY County.COUNTY, OLDTEST.BatchTrackID;

At this point, I've been joining (Query2.BatchTrackID and
Q1.CheckCounty) the results of these 2 queries with a 3rd query and
getting expected results.

The query below is my attempt to accomplish the same thing in a singel
query. The data in the resulting table make absolutely no sense.

QueriesCombined
SELECT OLDTEST.BatchTrackID, Count(OLDTEST.BatchTrackID) AS
CountOfBatchTrackID, Sum(tblCheckOffAndDispostionSheet.Quantity) AS
SumOfQuantity
FROM OLDTEST RIGHT JOIN tblCheckOffAndDispostionSheet ON
OLDTEST.BatchTrackID = tblCheckOffAndDispostionSheet.CheckCounty
WHERE (((OLDTEST.ScanGroup)="YG") AND
((tblCheckOffAndDispostionSheet.ScanGroup)="YG"))
GROUP BY OLDTEST.BatchTrackID;

I would truly appreciate it if someone could shed some light on this
for me.

Thanks in advance.

Tonk

 

[Michel Walsh]

Hi,


What about


SELECT a.*, b.*
FROM Query1 AS a INNER JOIN Query2 AS b
ON a.county=b.county AND b.CheckCounty = a.BatchTrackID



Hoping it may help,
Vanderghast, Access MVP