Too Many Queries

[knowshowrosegrows]

OK this is a series of 4 queries that are all tied together to get me the
final query. Can someone help me understand how to make them into one or
maybe two queries?

“qryMaxEffDateâ€

SELECT tblDPASCap.DPAS, Max(tblDPASCap.EFFECTIVE) AS MaxOfEFFECTIVE
FROM tblDPASCap
GROUP BY tblDPASCap.DPAS;

“qryGetCurrentCapOneâ€

SELECT tblProgram_Details.Program_ID, tblProgram_Details.DPAS_Code,
qryMaxEffDate.MaxOfEFFECTIVE
FROM tblProgram_Details LEFT JOIN qryMaxEffDate ON
tblProgram_Details.DPAS_Code = qryMaxEffDate.DPAS;

“qryGetCurrentCapTwoâ€

SELECT qryGetCurrentCapOne.DPAS_Code, qryGetCurrentCapOne.MaxOfEFFECTIVE,
tblDPASCap.CAPACITY, qryGetCurrentCapOne.Program_ID
FROM qryGetCurrentCapOne LEFT JOIN tblDPASCap ON
(qryGetCurrentCapOne.DPAS_Code = tblDPASCap.DPAS) AND
(qryGetCurrentCapOne.MaxOfEFFECTIVE = tblDPASCap.EFFECTIVE);

“qryGetCurrentCapThreeâ€

SELECT Sum(qryGetCurrentCapTwo.CAPACITY) AS DPASTotalCap,
tblProgram.Program_ID
FROM tblProgram LEFT JOIN qryGetCurrentCapTwo ON tblProgram.Program_ID =
qryGetCurrentCapTwo.Program_ID
GROUP BY tblProgram.Program_ID;

Ultimately, I am trying to the get the Maximum EFFECTIVE Date for a bunch of
DPAS_Codes (qryMaxEffDate) and the associated Program_ID for each DPAS_Code
(qryGetCurrentCapOne). Then I want the Sum of the Capacities that were in
effect on those Maximum Effective Dates for all the DPAS_Codes that relate to
each Program_ID (qryGetCurrentCapTwo) and (qryGetCurrentCapThree).

 

[Jeff Boyce]

Why?

I appreciate "elegance" as much as the next person, but why do you care
whether it takes a single query or a chain of 10 queries to get the answer
you need?


Regards

Jeff Boyce
Microsoft Office/Access MVP