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?
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?