Can These Queries Be Combined?

  • Thread starter Thread starter Tonk
  • Start date Start date
T

Tonk

Dear NG - Brand new to Access so I thought it would be best to try to
learn the right way right from the start.

I've used the following two queries to generate a list of names. My
instinct tells me that there is probably a more efficient and elegant
way of doing this. If you have a minute and don't mind helping out, I
sure would appreciate your feedback. The first query idenifies those
hunters who harvested 2 antlered bucks. The query captures their DL
and first and last name. I save the results of this query and then
join this query with all of the harvest-related information in
tbl2006Hrvst using DLInterface. Can this be done in a single query?

Thanks very much.

Mike



This is the first query:

SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName
FROM tbl2006Hrvst
GROUP BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName, tbl2006Hrvst.Deer
HAVING (((tbl2006Hrvst.Deer)="Antlered") AND
((Count(tbl2006Hrvst.DLInterface))=2))
ORDER BY Count(tbl2006Hrvst.DLInterface) DESC;

This is the second query:

SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName, tbl2006Hrvst.MiddleInitial,
tbl2006Hrvst.DateOfBirth, tbl2006Hrvst.City, tbl2006Hrvst.State,
tbl2006Hrvst.Zip, tbl2006Hrvst.CheckCounty, tbl2006Hrvst.CheckStation,
tbl2006Hrvst.county, tbl2006Hrvst.Metal, tbl2006Hrvst.Weapon,
tbl2006Hrvst.Deer, tbl2006Hrvst.Permit, tbl2006Hrvst.InvalidKillCheck,
tbl2006Hrvst.HarvestTimeStamp, tbl2006Hrvst.CheckTimeStamp,
tbl2006Hrvst.CheckDateStatusCode, tbl2006Hrvst.HrvstDateStatusCode,
tbl2006Hrvst.BatchNo, tbl2006Hrvst.BatchPgNo, tbl2006Hrvst.CSID,
tbl2006Hrvst.HarvestDate, tbl2006Hrvst.CheckDate,
tbl2006Hrvst.TimeOfHarvest, tbl2006Hrvst.TimeOfCheck,
tbl2006Hrvst.BatchTrackID, tbl2006Hrvst.Processed,
tbl2006Hrvst.CountyFlag, tbl2006Hrvst.CheckDateFlag,
tbl2006Hrvst.HarvestDateFlag, tbl2006Hrvst.CheckStationFlag,
tbl2006Hrvst.CheckCountyFlag, tbl2006Hrvst.WeaponFlag,
tbl2006Hrvst.DeerFlag, tbl2006Hrvst.PermitFlag,
tbl2006Hrvst.HrvstCheckStatus, tbl2006Hrvst.Vname
FROM qryMultipleBucks INNER JOIN tbl2006Hrvst ON
qryMultipleBucks.DLInterface = tbl2006Hrvst.DLInterface
WHERE (((tbl2006Hrvst.Deer)="Antlered"))
ORDER BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName;
 
Yes, they can be combined.

SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName, tbl2006Hrvst.MiddleInitial,
tbl2006Hrvst.DateOfBirth, tbl2006Hrvst.City, tbl2006Hrvst.State,
tbl2006Hrvst.Zip, tbl2006Hrvst.CheckCounty, tbl2006Hrvst.CheckStation,
tbl2006Hrvst.county, tbl2006Hrvst.Metal, tbl2006Hrvst.Weapon,
tbl2006Hrvst.Deer, tbl2006Hrvst.Permit, tbl2006Hrvst.InvalidKillCheck,
tbl2006Hrvst.HarvestTimeStamp, tbl2006Hrvst.CheckTimeStamp,
tbl2006Hrvst.CheckDateStatusCode, tbl2006Hrvst.HrvstDateStatusCode,
tbl2006Hrvst.BatchNo, tbl2006Hrvst.BatchPgNo, tbl2006Hrvst.CSID,
tbl2006Hrvst.HarvestDate, tbl2006Hrvst.CheckDate,
tbl2006Hrvst.TimeOfHarvest, tbl2006Hrvst.TimeOfCheck,
tbl2006Hrvst.BatchTrackID, tbl2006Hrvst.Processed,
tbl2006Hrvst.CountyFlag, tbl2006Hrvst.CheckDateFlag,
tbl2006Hrvst.HarvestDateFlag, tbl2006Hrvst.CheckStationFlag,
tbl2006Hrvst.CheckCountyFlag, tbl2006Hrvst.WeaponFlag,
tbl2006Hrvst.DeerFlag, tbl2006Hrvst.PermitFlag,
tbl2006Hrvst.HrvstCheckStatus, tbl2006Hrvst.Vname
FROM
(
SELECT tbl2006Hrvst.DLInterface
FROM tbl2006Hrvst
WHERE Deer = "Antlered"
GROUP BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName,
tbl2006Hrvst.Deer
HAVING Count(tbl2006Hrvst.DLInterface=2)
)
as qryMultipleBucks

INNER JOIN tbl2006Hrvst ON
qryMultipleBucks.DLInterface = tbl2006Hrvst.DLInterface
WHERE tbl2006Hrvst.Deer="Antlered"
ORDER BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName;
 
John - I did have a follow up question regarding the "combined" query.
I had to make some minor tweaks to get the thing to go. The final
looks like the following: If you'll scroll down a bit, you'll find my
question. In essence, what is puzzling is the fact that I make
reference to 1 of the original queries (MultipleBucks) on two
ocassions, which I deleted after combining the two queries, yet the new
query works just fine. How is this possible? (See below)

SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName, tbl2006Hrvst.MiddleInitial,
tbl2006Hrvst.DateOfBirth, tbl2006Hrvst.City, tbl2006Hrvst.State,
tbl2006Hrvst.Zip, tbl2006Hrvst.CheckCounty, tbl2006Hrvst.CheckStation,
tbl2006Hrvst.county, tbl2006Hrvst.Metal, tbl2006Hrvst.Weapon,
tbl2006Hrvst.Deer, tbl2006Hrvst.Permit, tbl2006Hrvst.InvalidKillCheck,
tbl2006Hrvst.HarvestTimeStamp, tbl2006Hrvst.CheckTimeStamp,
tbl2006Hrvst.CheckDateStatusCode, tbl2006Hrvst.HrvstDateStatusCode,
tbl2006Hrvst.BatchNo, tbl2006Hrvst.BatchPgNo, tbl2006Hrvst.CSID,
tbl2006Hrvst.HarvestDate, tbl2006Hrvst.CheckDate,
tbl2006Hrvst.TimeOfHarvest, tbl2006Hrvst.TimeOfCheck,
tbl2006Hrvst.BatchTrackID, tbl2006Hrvst.Processed,
tbl2006Hrvst.CountyFlag, tbl2006Hrvst.CheckDateFlag,
tbl2006Hrvst.HarvestDateFlag, tbl2006Hrvst.CheckStationFlag,
tbl2006Hrvst.CheckCountyFlag, tbl2006Hrvst.WeaponFlag,
tbl2006Hrvst.DeerFlag, tbl2006Hrvst.PermitFlag,
tbl2006Hrvst.HrvstCheckStatus, tbl2006Hrvst.Vname
FROM [SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName
FROM tbl2006Hrvst
GROUP BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName, tbl2006Hrvst.Deer
HAVING (((tbl2006Hrvst.Deer)="Antlered") AND
((Count(tbl2006Hrvst.DLInterface))=2))
ORDER BY Count(tbl2006Hrvst.DLInterface)]. AS

qryMultipleBucks (I DELETED THE QUERY MultipleBucks) INNER JOIN
tbl2006Hrvst ON qryMultipleBucks.DLInterface (AGAIN - I DELETED THE
QUERY MultipleBucks)

= tbl2006Hrvst.DLInterface
WHERE (((tbl2006Hrvst.Deer)="Antlered"))
ORDER BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName;
 
The Subquery you have embedded in the FROM clause is now named
qryMultipleBucks. Thats what the AS qryMultipleBucks does.

If that isn't what you mean, then I misinterprted your question.

Tonk said:
John - I did have a follow up question regarding the "combined" query.
I had to make some minor tweaks to get the thing to go. The final
looks like the following: If you'll scroll down a bit, you'll find my
question. In essence, what is puzzling is the fact that I make
reference to 1 of the original queries (MultipleBucks) on two
ocassions, which I deleted after combining the two queries, yet the new
query works just fine. How is this possible? (See below)

SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName, tbl2006Hrvst.MiddleInitial,
tbl2006Hrvst.DateOfBirth, tbl2006Hrvst.City, tbl2006Hrvst.State,
tbl2006Hrvst.Zip, tbl2006Hrvst.CheckCounty, tbl2006Hrvst.CheckStation,
tbl2006Hrvst.county, tbl2006Hrvst.Metal, tbl2006Hrvst.Weapon,
tbl2006Hrvst.Deer, tbl2006Hrvst.Permit, tbl2006Hrvst.InvalidKillCheck,
tbl2006Hrvst.HarvestTimeStamp, tbl2006Hrvst.CheckTimeStamp,
tbl2006Hrvst.CheckDateStatusCode, tbl2006Hrvst.HrvstDateStatusCode,
tbl2006Hrvst.BatchNo, tbl2006Hrvst.BatchPgNo, tbl2006Hrvst.CSID,
tbl2006Hrvst.HarvestDate, tbl2006Hrvst.CheckDate,
tbl2006Hrvst.TimeOfHarvest, tbl2006Hrvst.TimeOfCheck,
tbl2006Hrvst.BatchTrackID, tbl2006Hrvst.Processed,
tbl2006Hrvst.CountyFlag, tbl2006Hrvst.CheckDateFlag,
tbl2006Hrvst.HarvestDateFlag, tbl2006Hrvst.CheckStationFlag,
tbl2006Hrvst.CheckCountyFlag, tbl2006Hrvst.WeaponFlag,
tbl2006Hrvst.DeerFlag, tbl2006Hrvst.PermitFlag,
tbl2006Hrvst.HrvstCheckStatus, tbl2006Hrvst.Vname
FROM [SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName
FROM tbl2006Hrvst
GROUP BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName, tbl2006Hrvst.Deer
HAVING (((tbl2006Hrvst.Deer)="Antlered") AND
((Count(tbl2006Hrvst.DLInterface))=2))
ORDER BY Count(tbl2006Hrvst.DLInterface)]. AS

qryMultipleBucks (I DELETED THE QUERY MultipleBucks) INNER JOIN
tbl2006Hrvst ON qryMultipleBucks.DLInterface (AGAIN - I DELETED THE
QUERY MultipleBucks)

= tbl2006Hrvst.DLInterface
WHERE (((tbl2006Hrvst.Deer)="Antlered"))
ORDER BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName;



John said:
Yes, they can be combined.

SELECT tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName, tbl2006Hrvst.MiddleInitial,
tbl2006Hrvst.DateOfBirth, tbl2006Hrvst.City, tbl2006Hrvst.State,
tbl2006Hrvst.Zip, tbl2006Hrvst.CheckCounty, tbl2006Hrvst.CheckStation,
tbl2006Hrvst.county, tbl2006Hrvst.Metal, tbl2006Hrvst.Weapon,
tbl2006Hrvst.Deer, tbl2006Hrvst.Permit, tbl2006Hrvst.InvalidKillCheck,
tbl2006Hrvst.HarvestTimeStamp, tbl2006Hrvst.CheckTimeStamp,
tbl2006Hrvst.CheckDateStatusCode, tbl2006Hrvst.HrvstDateStatusCode,
tbl2006Hrvst.BatchNo, tbl2006Hrvst.BatchPgNo, tbl2006Hrvst.CSID,
tbl2006Hrvst.HarvestDate, tbl2006Hrvst.CheckDate,
tbl2006Hrvst.TimeOfHarvest, tbl2006Hrvst.TimeOfCheck,
tbl2006Hrvst.BatchTrackID, tbl2006Hrvst.Processed,
tbl2006Hrvst.CountyFlag, tbl2006Hrvst.CheckDateFlag,
tbl2006Hrvst.HarvestDateFlag, tbl2006Hrvst.CheckStationFlag,
tbl2006Hrvst.CheckCountyFlag, tbl2006Hrvst.WeaponFlag,
tbl2006Hrvst.DeerFlag, tbl2006Hrvst.PermitFlag,
tbl2006Hrvst.HrvstCheckStatus, tbl2006Hrvst.Vname
FROM
(
SELECT tbl2006Hrvst.DLInterface
FROM tbl2006Hrvst
WHERE Deer = "Antlered"
GROUP BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.MiddleInitial, tbl2006Hrvst.FirstName,
tbl2006Hrvst.Deer
HAVING Count(tbl2006Hrvst.DLInterface=2)
)
as qryMultipleBucks

INNER JOIN tbl2006Hrvst ON
qryMultipleBucks.DLInterface = tbl2006Hrvst.DLInterface
WHERE tbl2006Hrvst.Deer="Antlered"
ORDER BY tbl2006Hrvst.DLInterface, tbl2006Hrvst.LastName,
tbl2006Hrvst.FirstName;
Click to expand...
Click to expand...
 
Back
Top