SubQuery Assistance...

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.
 
A

Allen Browne

The error occurs because CCur() cannot handle nulls.

Either remove the CCur(), or use Nz() to convert the null to zero:
CCur(Nz((SELECT TOP 1 ...) ,0))

I didn't work through the last part of your query, but if you just want
monthly totals, it might be easier to create a query that gives you the
totals per month, save it, and then build your next query on top of that.
(Hope I've understood what you asked.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top