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