Calculating totals on report.

L

Lynne

Hi

I've a report which needs to calculate a period to date figure depending on
the period number which appears on it.

I've tried using an iif expression, but it's far too long and cannot display.

Can anyone help?
 
D

Duane Hookom

Do you have a table/query structure you would like to share as well as some
sample records?
 
W

Wayne Morgan

You may have better luck with a DSum() function based on the report's record
source. You would use the date period on the report for the Where part
filter of the DSum() function.

Example:
=DSum("[MyField]", "[ReportControlSourceName]", "[DateField]>= #" &
[ReportStartDate] & "# And [DateField] <= #" & Date() & "#")
 
L

Lynne

At the moment, my sql reads as follows:

SELECT dbo_MSLiveDetailedTB.trans_period, dbo_MSLiveDetailedTB.nlyear,
dbo_MSLiveDetailedTB.nominal_code, dbo_MSLiveDetailedTB.description,
dbo_MSLiveDetailedTB.journal_date, dbo_MSLiveDetailedTB.journal_number,
dbo_MSLiveDetailedTB.journal_desc, dbo_MSLiveDetailedTB.journal_amount,
dbo_MSLiveDetailedTB.base_amount01, dbo_MSLiveDetailedTB.base_amount02,
dbo_MSLiveDetailedTB.base_amount03, dbo_MSLiveDetailedTB.base_amount04,
dbo_MSLiveDetailedTB.base_amount05, dbo_MSLiveDetailedTB.base_amount06,
dbo_MSLiveDetailedTB.base_amount07, dbo_MSLiveDetailedTB.base_amount08,
dbo_MSLiveDetailedTB.base_amount09, dbo_MSLiveDetailedTB.base_amount10,
dbo_MSLiveDetailedTB.base_amount11, dbo_MSLiveDetailedTB.base_amount12,
dbo_MSLiveDetailedTB.period_actual01, dbo_MSLiveDetailedTB.period_actual02,
dbo_MSLiveDetailedTB.period_actual03, dbo_MSLiveDetailedTB.period_actual04,
dbo_MSLiveDetailedTB.period_actual05, dbo_MSLiveDetailedTB.period_actual06,
dbo_MSLiveDetailedTB.period_actual07, dbo_MSLiveDetailedTB.period_actual08,
dbo_MSLiveDetailedTB.period_actual09, dbo_MSLiveDetailedTB.period_actual10,
dbo_MSLiveDetailedTB.period_actual11, dbo_MSLiveDetailedTB.period_actual12,
dbo_MSLiveDetailedTB.budget_code,
IIf(Forms!Selection!Periods=1,[base_amount01],IIf(Forms!Selection!Periods=2,[base_amount02],IIf(Forms!Selection!Periods=3,[base_amount03],IIf(Forms!Selection!Periods=4,[base_amount04],IIf(Forms!Selection!Periods=5,[base_amount05],IIf(Forms!Selection!Periods=6,[base_amount06],IIf(Forms!Selection!Periods=7,[base_amount07],IIf(Forms!Selection!Periods=8,[base_amount08],IIf(Forms!Selection!Periods=9,[base_amount09],IIf(Forms!Selection!Periods=10,[base_amount10],IIf(Forms!Selection!Periods=11,[base_amount11],IIf(Forms!Selection!Periods=12,[base_amount12],""))))))))))))
AS Exp1,
IIf(Forms!Selection!Periods=1,[period_actual12],IIf(Forms!Selection!Periods=2,[period_actual01],IIf(Forms!Selection!Periods=3,[period_actual02],IIf(Forms!Selection!Periods=4,[period_actual03],IIf(Forms!Selection!Periods=5,[period_actual04],IIf(Forms!Selection!Periods=6,[period_actual05],IIf(Forms!Selection!Periods=7,[period_actual06],IIf(Forms!Selection!Periods=8,[period_actual07],IIf(Forms!Selection!Periods=9,[period_actual08],IIf(Forms!Selection!Periods=10,[period_actual09],IIf(Forms!Selection!Periods=11,[period_actual10],IIf(Forms!Selection!Periods=12,[period_actual11],"")))))))))))) AS Exp2
FROM dbo_MSLiveDetailedTB
WHERE (((dbo_MSLiveDetailedTB.trans_period)=[Forms]![Selection]![Periods])
AND ((dbo_MSLiveDetailedTB.nlyear)=[Forms]![Selection]![Years]));

What I am trying to achieve is this for example......
if forms!Selection!periods=2 then [period_actual01]+[period_actual02]
going through for period 3 would be period01+02+03 etc..
 
D

Duane Hookom

I would use a union query to normalize the data. You could then quite easily
sum values by month/period.

--
Duane Hookom
MS Access MVP


Lynne said:
At the moment, my sql reads as follows:

SELECT dbo_MSLiveDetailedTB.trans_period, dbo_MSLiveDetailedTB.nlyear,
dbo_MSLiveDetailedTB.nominal_code, dbo_MSLiveDetailedTB.description,
dbo_MSLiveDetailedTB.journal_date, dbo_MSLiveDetailedTB.journal_number,
dbo_MSLiveDetailedTB.journal_desc, dbo_MSLiveDetailedTB.journal_amount,
dbo_MSLiveDetailedTB.base_amount01, dbo_MSLiveDetailedTB.base_amount02,
dbo_MSLiveDetailedTB.base_amount03, dbo_MSLiveDetailedTB.base_amount04,
dbo_MSLiveDetailedTB.base_amount05, dbo_MSLiveDetailedTB.base_amount06,
dbo_MSLiveDetailedTB.base_amount07, dbo_MSLiveDetailedTB.base_amount08,
dbo_MSLiveDetailedTB.base_amount09, dbo_MSLiveDetailedTB.base_amount10,
dbo_MSLiveDetailedTB.base_amount11, dbo_MSLiveDetailedTB.base_amount12,
dbo_MSLiveDetailedTB.period_actual01,
dbo_MSLiveDetailedTB.period_actual02,
dbo_MSLiveDetailedTB.period_actual03,
dbo_MSLiveDetailedTB.period_actual04,
dbo_MSLiveDetailedTB.period_actual05,
dbo_MSLiveDetailedTB.period_actual06,
dbo_MSLiveDetailedTB.period_actual07,
dbo_MSLiveDetailedTB.period_actual08,
dbo_MSLiveDetailedTB.period_actual09,
dbo_MSLiveDetailedTB.period_actual10,
dbo_MSLiveDetailedTB.period_actual11,
dbo_MSLiveDetailedTB.period_actual12,
dbo_MSLiveDetailedTB.budget_code,
IIf(Forms!Selection!Periods=1,[base_amount01],IIf(Forms!Selection!Periods=2,[base_amount02],IIf(Forms!Selection!Periods=3,[base_amount03],IIf(Forms!Selection!Periods=4,[base_amount04],IIf(Forms!Selection!Periods=5,[base_amount05],IIf(Forms!Selection!Periods=6,[base_amount06],IIf(Forms!Selection!Periods=7,[base_amount07],IIf(Forms!Selection!Periods=8,[base_amount08],IIf(Forms!Selection!Periods=9,[base_amount09],IIf(Forms!Selection!Periods=10,[base_amount10],IIf(Forms!Selection!Periods=11,[base_amount11],IIf(Forms!Selection!Periods=12,[base_amount12],""))))))))))))
AS Exp1,
IIf(Forms!Selection!Periods=1,[period_actual12],IIf(Forms!Selection!Periods=2,[period_actual01],IIf(Forms!Selection!Periods=3,[period_actual02],IIf(Forms!Selection!Periods=4,[period_actual03],IIf(Forms!Selection!Periods=5,[period_actual04],IIf(Forms!Selection!Periods=6,[period_actual05],IIf(Forms!Selection!Periods=7,[period_actual06],IIf(Forms!Selection!Periods=8,[period_actual07],IIf(Forms!Selection!Periods=9,[period_actual08],IIf(Forms!Selection!Periods=10,[period_actual09],IIf(Forms!Selection!Periods=11,[period_actual10],IIf(Forms!Selection!Periods=12,[period_actual11],""))))))))))))
AS Exp2
FROM dbo_MSLiveDetailedTB
WHERE (((dbo_MSLiveDetailedTB.trans_period)=[Forms]![Selection]![Periods])
AND ((dbo_MSLiveDetailedTB.nlyear)=[Forms]![Selection]![Years]));

What I am trying to achieve is this for example......
if forms!Selection!periods=2 then [period_actual01]+[period_actual02]
going through for period 3 would be period01+02+03 etc..

Duane Hookom said:
Do you have a table/query structure you would like to share as well as
some
sample records?
Click to expand...
Click to expand...
 
Top