Repeating a Total in Mutiple Records.

J

John

I have a table with several months of monthly reports (One for each month.),
I have a planned percent complete in another table. The planned percent
complete * total budgeted cost = planned cost for the month. I would like to
show this data in a query with the following fields:

Period, Date, Planned Percent, Planned Cost

Because the budgeted cost changes from month-to-month (change orders, added
work, etc.) we hold the planned percent per month, and us it times the
current budget to gage progress on the project.

I have the following query:

PARAMETERS [forms]![frmProjectCost]![cmbCurMon] DateTime;
SELECT tblProjInfo.Period AS Prd, Format([Period_Dte],"mmm-yy") AS RptDte,
First(tblProjInfo.PlanPct) AS FirstOfPlanPct, Sum((SELECT CB.budgt_cur FROM
tblMonthly AS CB WHERE CB.FnDte =
[forms]![frmProjectCost]![CmbCurMon])*[tblProjInfo]![PlanPct]) AS PCst
FROM tblProjInfo LEFT JOIN tblMonthly ON tblProjInfo.Period_Dte =
tblMonthly.FnDte
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy")
HAVING (((tblProjInfo.Period)>-1));

When I run this I get the error: "At most only one record can be returned
by this subquery"

This is exactly what I want, one total value, mutiplied by each planned
percent for the month. How do I tell Access to repeate the same total for
every month?
 
K

KARL DEWEY

I do not quite follow but try this --
PARAMETERS [forms]![frmProjectCost]![cmbCurMon] DateTime;
SELECT tblProjInfo.Period AS Prd, Format([Period_Dte],"mmm-yy") AS RptDte,
First(tblProjInfo.PlanPct) AS FirstOfPlanPct, CB.budgt_cur *
[tblProjInfo]![PlanPct]) AS PCst
FROM tblProjInfo LEFT JOIN tblMonthly ON tblProjInfo.Period_Dte =
tblMonthly.FnDte
WHERE ((tblProjInfo.Period)>-1) AND CB.FnDte =
[forms]![frmProjectCost]![CmbCurMon]
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy");
 
J

John

Karl,
Thanks for the response, but it didn't work... I'll try to explain a little
better. The data looks something like this:

tblProjInfo has:
period (integer)
period_dte (Date - One)
Plan_Pct (percent)

There are only about 50 records in this table, one for each month of
construction.

tblMonthly has: (lots of things, but the fields of interest):
FnDte (Date - many)
budgt_cur (Curancy)

What I need is to sum up all the budgt_cur for the date report date
selected on a form (say 8/31/08) which is
[forms]![frmProjectCost]![CmbCurMon] to get a total budgeted cost
(333,111,304 for example) then take that total and multiply it by all the
plan_Pct values in tblProjInfo to get a plan earned cost per month, given the
current total budget. The output would look something like:

Period - Date - PlanPct - Budget - Plan Earned
1 Dec-07 25% 333,111,304 83,277,826
2 Jan-08 25% 333,111,304 83,277,826
3 Feb-08 25% 333,111,304 83,277,826
4 Mar-08 25% 333,111,304 83,277,826

Of course, there are many more than 4 months, but you get the idea (I hope).
I was trying to use a subquery to sum up the total for the month, then use
that one record to mutiply by the plan percent.

Hope this helps...

--
Thanks in advance!
**John**


KARL DEWEY said:
I do not quite follow but try this --
PARAMETERS [forms]![frmProjectCost]![cmbCurMon] DateTime;
SELECT tblProjInfo.Period AS Prd, Format([Period_Dte],"mmm-yy") AS RptDte,
First(tblProjInfo.PlanPct) AS FirstOfPlanPct, CB.budgt_cur *
[tblProjInfo]![PlanPct]) AS PCst
FROM tblProjInfo LEFT JOIN tblMonthly ON tblProjInfo.Period_Dte =
tblMonthly.FnDte
WHERE ((tblProjInfo.Period)>-1) AND CB.FnDte =
[forms]![frmProjectCost]![CmbCurMon]
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy");

--
KARL DEWEY
Build a little - Test a little


John said:
I have a table with several months of monthly reports (One for each month.),
I have a planned percent complete in another table. The planned percent
complete * total budgeted cost = planned cost for the month. I would like to
show this data in a query with the following fields:

Period, Date, Planned Percent, Planned Cost

Because the budgeted cost changes from month-to-month (change orders, added
work, etc.) we hold the planned percent per month, and us it times the
current budget to gage progress on the project.

I have the following query:

PARAMETERS [forms]![frmProjectCost]![cmbCurMon] DateTime;
SELECT tblProjInfo.Period AS Prd, Format([Period_Dte],"mmm-yy") AS RptDte,
First(tblProjInfo.PlanPct) AS FirstOfPlanPct, Sum((SELECT CB.budgt_cur FROM
tblMonthly AS CB WHERE CB.FnDte =
[forms]![frmProjectCost]![CmbCurMon])*[tblProjInfo]![PlanPct]) AS PCst
FROM tblProjInfo LEFT JOIN tblMonthly ON tblProjInfo.Period_Dte =
tblMonthly.FnDte
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy")
HAVING (((tblProjInfo.Period)>-1));

When I run this I get the error: "At most only one record can be returned
by this subquery"

This is exactly what I want, one total value, mutiplied by each planned
percent for the month. How do I tell Access to repeate the same total for
every month?
 

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

Similar Threads

Subquery Assistance 3
CrossTab Help! 6

Top