J
John
I have a query as follows:
SELECT tblMonthly.act, tblProjInfo.Period, tblMonthly.FnDte AS RptDate,
tblMonthly.budgl_cur AS BudgetLabor, [tblMonthly].[budgl_cur]-
CCur((SELECT TOP 1 tblDupe.budgl_cur FROM tblMonthly AS tblDupe WHERE
tblDupe.act = tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY
tblDupe.FnDte Desc, tblDupe.act)) AS BudLbrChange
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
GROUP BY tblMonthly.act, tblProjInfo.Period, tblMonthly.FnDte,
tblMonthly.budgl_cur
HAVING (((tblMonthly.budgl_cur)>0))
ORDER BY tblMonthly.act, tblProjInfo.Period;
It gives me monthly budgets using a subquery to get the budget for last
month. It works ok (gives me an "#error" on the first period of each
tblMonthly.act because there is no prior month - any help here?). I then
take the current budget - last month's budget to get the budget change
[BudLbrChange]. I would like to change this query to show only monthly
totals so it would have:
count(tblMonthly.act)
Sum(tblMonthly.budgl_cur)
Sum(BudLbrChange)
Group by tblProjInfo.period, tblMonthly.FnDte
I would need to remove the "tblDupe.act = tblMonthly.act" from the subquery
because I want it to sum on the finish date only (FnDte). I can't seem to
get this to work! I keep getting errors such as "... can do it because 'act'
is not part of an aggregate function" or it shows me the subquery and says
there is a syntax error.
How do you take a detailed query with a sub query and turn it into a summary
query?
Any help would be appreciated.
SELECT tblMonthly.act, tblProjInfo.Period, tblMonthly.FnDte AS RptDate,
tblMonthly.budgl_cur AS BudgetLabor, [tblMonthly].[budgl_cur]-
CCur((SELECT TOP 1 tblDupe.budgl_cur FROM tblMonthly AS tblDupe WHERE
tblDupe.act = tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY
tblDupe.FnDte Desc, tblDupe.act)) AS BudLbrChange
FROM tblMonthly LEFT JOIN tblProjInfo ON tblMonthly.period =
tblProjInfo.CMIS_Mo
GROUP BY tblMonthly.act, tblProjInfo.Period, tblMonthly.FnDte,
tblMonthly.budgl_cur
HAVING (((tblMonthly.budgl_cur)>0))
ORDER BY tblMonthly.act, tblProjInfo.Period;
It gives me monthly budgets using a subquery to get the budget for last
month. It works ok (gives me an "#error" on the first period of each
tblMonthly.act because there is no prior month - any help here?). I then
take the current budget - last month's budget to get the budget change
[BudLbrChange]. I would like to change this query to show only monthly
totals so it would have:
count(tblMonthly.act)
Sum(tblMonthly.budgl_cur)
Sum(BudLbrChange)
Group by tblProjInfo.period, tblMonthly.FnDte
I would need to remove the "tblDupe.act = tblMonthly.act" from the subquery
because I want it to sum on the finish date only (FnDte). I can't seem to
get this to work! I keep getting errors such as "... can do it because 'act'
is not part of an aggregate function" or it shows me the subquery and says
there is a syntax error.
How do you take a detailed query with a sub query and turn it into a summary
query?
Any help would be appreciated.