how to combine results of these two queries - I'm stumped!

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

Harold Good

Hi,
I have a real stumper for me, so much so that I hardly know how to describe
it in standard terminology because I don't know Access terms that well.

I have the TSC_Projects table and the TBL_Project_Financials table linked on
ProjectID. I want the result of a single query to use for a Report. I want
the results to include the fields ProjectID, FiscalYear, AnnualBudget,
YTDExpense and ActiveProject. Typically projects are clicked Inactive when
the goals are completed and no budget needed, thus they would not appear in
this report. However, sometimes our projects have the goals complete, but
some administrative cleanup work to be done, so they are still Active and
should appear, even though there is no budget for them.

Query 1 simply finds the Active projects in the current Fiscal Year and
displays the other fields as per above. No problem so far.

The challenge is for those projects that are still Active, but have no
budget for FY09. Since no budget exists for FY09, these Projects do not
appear in Query 1, but they should since they are still Active.

I've come up with an amatuer attempt in Query 2 that captures those projects
without budgets for FY09.

But now what do I do next? I've scoured the internet, experimenting with
Union Query, I've read a bit about Exists, etc, but I don't know how to pull
this together. Quite possibly it could be done in a singe query.

If any of you have any thoughts on this, I would surely appreciate it. See
the Sql below (it will become evident I don't have a clue!)


Qry1
SELECT TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
TBL_PROJECT_FINANCIALS.AnnualBudget, TBL_PROJECT_FINANCIALS.FiscalYear,
TBL_PROJECT_FINANCIALS.YTDExpense, TSC_PROJECTS.ProjectName
FROM TSC_PROJECTS INNER JOIN TBL_PROJECT_FINANCIALS ON
TSC_PROJECTS.ProjectID = TBL_PROJECT_FINANCIALS.ProjectID
WHERE (((TSC_PROJECTS.Active_Project)=True) AND
((TBL_PROJECT_FINANCIALS.FiscalYear)=Year(DateAdd("m",3,Now()))));

Qry2
SELECT TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
Last(TBL_PROJECT_FINANCIALS.AnnualBudget) AS LastOfAnnualBudget,
Max(TBL_PROJECT_FINANCIALS.FiscalYear) AS MaxOfFiscalYear,
Last(TBL_PROJECT_FINANCIALS.YTDExpense) AS LastOfYTDExpense,
TSC_PROJECTS.ProjectName
FROM TSC_PROJECTS INNER JOIN TBL_PROJECT_FINANCIALS ON
TSC_PROJECTS.ProjectID = TBL_PROJECT_FINANCIALS.ProjectID
GROUP BY TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
TSC_PROJECTS.ProjectName
HAVING (((TSC_PROJECTS.Active_Project)=True) AND
((Max(TBL_PROJECT_FINANCIALS.FiscalYear))=2008));
 
You might take a look at a UNION Query to combine the two

SELECT TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
TBL_PROJECT_FINANCIALS.AnnualBudget, TBL_PROJECT_FINANCIALS.FiscalYear,
TBL_PROJECT_FINANCIALS.YTDExpense, TSC_PROJECTS.ProjectName
FROM TSC_PROJECTS INNER JOIN TBL_PROJECT_FINANCIALS ON
TSC_PROJECTS.ProjectID = TBL_PROJECT_FINANCIALS.ProjectID
WHERE (((TSC_PROJECTS.Active_Project)=True) AND
((TBL_PROJECT_FINANCIALS.FiscalYear)=Year(DateAdd("m",3,Now()))))

UNION ALL

SELECT TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
Last(TBL_PROJECT_FINANCIALS.AnnualBudget) AS LastOfAnnualBudget,
Max(TBL_PROJECT_FINANCIALS.FiscalYear) AS MaxOfFiscalYear,
Last(TBL_PROJECT_FINANCIALS.YTDExpense) AS LastOfYTDExpense,
TSC_PROJECTS.ProjectName
FROM TSC_PROJECTS INNER JOIN TBL_PROJECT_FINANCIALS ON
TSC_PROJECTS.ProjectID = TBL_PROJECT_FINANCIALS.ProjectID
GROUP BY TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
TSC_PROJECTS.ProjectName
HAVING(((TSC_PROJECTS.Active_Project)=True) AND
((Max(TBL_PROJECT_FINANCIALS.FiscalYear))=2008));

HOWEVER, you still have a problem. LAST does not necessarily return the
LATEST. IT returns the last record accessed when the query executes for each
Group - often that is the last record added or modified for the group, but
that is not always what happens.

The following query might return what you are looking for in your second query
SELECT TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
TBL_PROJECT_FINANCIALS.AnnualBudget, TBL_PROJECT_FINANCIALS.FiscalYear,
TBL_PROJECT_FINANCIALS.YTDExpense, TSC_PROJECTS.ProjectName
FROM TSC_PROJECTS INNER JOIN TBL_PROJECT_FINANCIALS ON
TSC_PROJECTS.ProjectID = TBL_PROJECT_FINANCIALS.ProjectID
WHERE TSC_PROJECTS.Active_Project=True AND
TBL_PROJECT_FINANCIALS.FiscalYear=Year(DateAdd("m",3,Date()))-1

So you could UNION THAT instead of the query you have built.
======================================================================

SELECT TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
TBL_PROJECT_FINANCIALS.AnnualBudget, TBL_PROJECT_FINANCIALS.FiscalYear,
TBL_PROJECT_FINANCIALS.YTDExpense, TSC_PROJECTS.ProjectName
FROM TSC_PROJECTS INNER JOIN TBL_PROJECT_FINANCIALS ON
TSC_PROJECTS.ProjectID = TBL_PROJECT_FINANCIALS.ProjectID
WHERE (((TSC_PROJECTS.Active_Project)=True) AND
((TBL_PROJECT_FINANCIALS.FiscalYear)=Year(DateAdd("m",3,Now()))))

UNION ALL

SELECT TBL_PROJECT_FINANCIALS.ProjectID, TSC_PROJECTS.Active_Project,
TBL_PROJECT_FINANCIALS.AnnualBudget, TBL_PROJECT_FINANCIALS.FiscalYear,
TBL_PROJECT_FINANCIALS.YTDExpense, TSC_PROJECTS.ProjectName
FROM TSC_PROJECTS INNER JOIN TBL_PROJECT_FINANCIALS ON
TSC_PROJECTS.ProjectID = TBL_PROJECT_FINANCIALS.ProjectID
WHERE TSC_PROJECTS.Active_Project=True AND
TBL_PROJECT_FINANCIALS.FiscalYear=Year(DateAdd("m",3,Date()))-1

========================================================================

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top