How calculate percent difference between fields in same group in a

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a report sectioned by year with number fields. I would like to
calculate percent difference of the number fields for each year.
 
If you just want to compare the value for one year with the value in the
previous record, and you are comfortable with VBA code, you could do it like
this:

a) Declare a variable in the General Declarations section of the report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to remember the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...
 
Allen,

Thanks for your response, but unfortunately I have very little experience
with VBA code outside usiing recommendations of others. I know my way around
Access 2003 pretty well and just cannot get over this hurdle. My report which
is based on a simple query looks similar to this:

2001 2002 2003 2004
Occasions 20 25 23 24
Attendance 224 125 122 200
Paper Sales 1000 1500 1250 1350

The data is located in the Year footer. I would like to calculate the
percent change in each variable for each year. For example, the calculation
for total yearly Attendance is =Sum([Persons Attending]). My calculation for
percent change of course comes to zero as i don't know the perform this type
of calculation whithin the footer... =(Sum([Persons Attending])-Sum([Persons
Attending]))...

Your help is greatly appreciated.

Allen Browne said:
If you just want to compare the value for one year with the value in the
previous record, and you are comfortable with VBA code, you could do it like
this:

a) Declare a variable in the General Declarations section of the report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to remember the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
i have a report sectioned by year with number fields. I would like to
calculate percent difference of the number fields for each year.
Click to expand...
Click to expand...
 
So the report has a *column* for each year?
And the column names are 2001, 2002, etc?

If so, you could add a text box to the Report Footer section, and give it
these properties:
Control Source =Sum([2001])
Name txtSum2001

Then you could show the percentage difference in another text box with
properties like this:
Control Source =([txtSum2002] - [txtSum2001]) / [txtSum2001]
Format Percent

Once it's working, you probably want to refine it to:
=IIf([txtSum2001]=0, Null, ([txtSum2002] - [txtSum2001]) / [txtSum2001])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
Allen,

Thanks for your response, but unfortunately I have very little experience
with VBA code outside usiing recommendations of others. I know my way
around
Access 2003 pretty well and just cannot get over this hurdle. My report
which
is based on a simple query looks similar to this:

2001 2002 2003 2004
Occasions 20 25 23 24
Attendance 224 125 122 200
Paper Sales 1000 1500 1250 1350

The data is located in the Year footer. I would like to calculate the
percent change in each variable for each year. For example, the
calculation
for total yearly Attendance is =Sum([Persons Attending]). My calculation
for
percent change of course comes to zero as i don't know the perform this
type
of calculation whithin the footer... =(Sum([Persons
Attending])-Sum([Persons
Attending]))...

Your help is greatly appreciated.

Allen Browne said:
If you just want to compare the value for one year with the value in the
previous record, and you are comfortable with VBA code, you could do it
like
this:

a) Declare a variable in the General Declarations section of the report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to remember
the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
i have a report sectioned by year with number fields. I would like to
calculate percent difference of the number fields for each year.
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen,

I'm am getting extremely close with the information provied from you thus
far. Still a few more steps to go...The report has one cloum for the year and
because of the way i have the report configured the yearly totals are
horizontially across the page. So when you see 2001 2002 2003 it is
actualy the ([Year]) field. This is where i get stuck. I have been
unsuccessful in writing an "IIF" statement to perform the calculations. I'll
try and explain using attendance...If year is 2001, calculate percent change
in attendance from 2000 to 2001...this would be repeated for each year.

Hope this helps...

Cecil


Allen Browne said:
So the report has a *column* for each year?
And the column names are 2001, 2002, etc?

If so, you could add a text box to the Report Footer section, and give it
these properties:
Control Source =Sum([2001])
Name txtSum2001

Then you could show the percentage difference in another text box with
properties like this:
Control Source =([txtSum2002] - [txtSum2001]) / [txtSum2001]
Format Percent

Once it's working, you probably want to refine it to:
=IIf([txtSum2001]=0, Null, ([txtSum2002] - [txtSum2001]) / [txtSum2001])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
Allen,

Thanks for your response, but unfortunately I have very little experience
with VBA code outside usiing recommendations of others. I know my way
around
Access 2003 pretty well and just cannot get over this hurdle. My report
which
is based on a simple query looks similar to this:

2001 2002 2003 2004
Occasions 20 25 23 24
Attendance 224 125 122 200
Paper Sales 1000 1500 1250 1350

The data is located in the Year footer. I would like to calculate the
percent change in each variable for each year. For example, the
calculation
for total yearly Attendance is =Sum([Persons Attending]). My calculation
for
percent change of course comes to zero as i don't know the perform this
type
of calculation whithin the footer... =(Sum([Persons
Attending])-Sum([Persons
Attending]))...

Your help is greatly appreciated.

Allen Browne said:
If you just want to compare the value for one year with the value in the
previous record, and you are comfortable with VBA code, you could do it
like
this:

a) Declare a variable in the General Declarations section of the report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to remember
the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

i have a report sectioned by year with number fields. I would like to
calculate percent difference of the number fields for each year.
Click to expand...
Click to expand...
Click to expand...
 
So you want something like this:
=([Yr2002] - [Yr2001]) / [Yr2001]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
Hi Allen,

I'm am getting extremely close with the information provied from you thus
far. Still a few more steps to go...The report has one cloum for the year
and
because of the way i have the report configured the yearly totals are
horizontially across the page. So when you see 2001 2002 2003 it
is
actualy the ([Year]) field. This is where i get stuck. I have been
unsuccessful in writing an "IIF" statement to perform the calculations.
I'll
try and explain using attendance...If year is 2001, calculate percent
change
in attendance from 2000 to 2001...this would be repeated for each year.

Hope this helps...

Cecil


Allen Browne said:
So the report has a *column* for each year?
And the column names are 2001, 2002, etc?

If so, you could add a text box to the Report Footer section, and give it
these properties:
Control Source =Sum([2001])
Name txtSum2001

Then you could show the percentage difference in another text box with
properties like this:
Control Source =([txtSum2002] - [txtSum2001]) / [txtSum2001]
Format Percent

Once it's working, you probably want to refine it to:
=IIf([txtSum2001]=0, Null, ([txtSum2002] - [txtSum2001]) /
[txtSum2001])

Cecil said:
Allen,

Thanks for your response, but unfortunately I have very little
experience
with VBA code outside usiing recommendations of others. I know my way
around
Access 2003 pretty well and just cannot get over this hurdle. My report
which
is based on a simple query looks similar to this:

2001 2002 2003 2004
Occasions 20 25 23 24
Attendance 224 125 122 200
Paper Sales 1000 1500 1250 1350

The data is located in the Year footer. I would like to calculate the
percent change in each variable for each year. For example, the
calculation
for total yearly Attendance is =Sum([Persons Attending]). My
calculation
for
percent change of course comes to zero as i don't know the perform this
type
of calculation whithin the footer... =(Sum([Persons
Attending])-Sum([Persons
Attending]))...

Your help is greatly appreciated.

:

If you just want to compare the value for one year with the value in
the
previous record, and you are comfortable with VBA code, you could do
it
like
this:

a) Declare a variable in the General Declarations section of the
report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to
remember
the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...

i have a report sectioned by year with number fields. I would like to
calculate percent difference of the number fields for each year.
Click to expand...
Click to expand...
Click to expand...
 
Allen,

Yes...but i don't know how to get to that point as I have only one field for
year...([Year]). At each change in year the group starts over. I somehow
need to add a field that can calculate percent change as you describe below
that recognizes the change in year.

Allen Browne said:
So you want something like this:
=([Yr2002] - [Yr2001]) / [Yr2001]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
Hi Allen,

I'm am getting extremely close with the information provied from you thus
far. Still a few more steps to go...The report has one cloum for the year
and
because of the way i have the report configured the yearly totals are
horizontially across the page. So when you see 2001 2002 2003 it
is
actualy the ([Year]) field. This is where i get stuck. I have been
unsuccessful in writing an "IIF" statement to perform the calculations.
I'll
try and explain using attendance...If year is 2001, calculate percent
change
in attendance from 2000 to 2001...this would be repeated for each year.

Hope this helps...

Cecil


Allen Browne said:
So the report has a *column* for each year?
And the column names are 2001, 2002, etc?

If so, you could add a text box to the Report Footer section, and give it
these properties:
Control Source =Sum([2001])
Name txtSum2001

Then you could show the percentage difference in another text box with
properties like this:
Control Source =([txtSum2002] - [txtSum2001]) / [txtSum2001]
Format Percent

Once it's working, you probably want to refine it to:
=IIf([txtSum2001]=0, Null, ([txtSum2002] - [txtSum2001]) /
[txtSum2001])

Allen,

Thanks for your response, but unfortunately I have very little
experience
with VBA code outside usiing recommendations of others. I know my way
around
Access 2003 pretty well and just cannot get over this hurdle. My report
which
is based on a simple query looks similar to this:

2001 2002 2003 2004
Occasions 20 25 23 24
Attendance 224 125 122 200
Paper Sales 1000 1500 1250 1350

The data is located in the Year footer. I would like to calculate the
percent change in each variable for each year. For example, the
calculation
for total yearly Attendance is =Sum([Persons Attending]). My
calculation
for
percent change of course comes to zero as i don't know the perform this
type
of calculation whithin the footer... =(Sum([Persons
Attending])-Sum([Persons
Attending]))...

Your help is greatly appreciated.

:

If you just want to compare the value for one year with the value in
the
previous record, and you are comfortable with VBA code, you could do
it
like
this:

a) Declare a variable in the General Declarations section of the
report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to
remember
the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...

i have a report sectioned by year with number fields. I would like to
calculate percent difference of the number fields for each year.
Click to expand...
Click to expand...
Click to expand...
 
Cecil, I don't think I can help you.

You don't have the years as columns, and you don't have them in the rows, so
I don't see anything you have that you can calculate a percentage of.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
Allen,

Yes...but i don't know how to get to that point as I have only one field
for
year...([Year]). At each change in year the group starts over. I somehow
need to add a field that can calculate percent change as you describe
below
that recognizes the change in year.

Allen Browne said:
So you want something like this:
=([Yr2002] - [Yr2001]) / [Yr2001]

Cecil said:
Hi Allen,

I'm am getting extremely close with the information provied from you
thus
far. Still a few more steps to go...The report has one cloum for the
year
and
because of the way i have the report configured the yearly totals are
horizontially across the page. So when you see 2001 2002 2003
it
is
actualy the ([Year]) field. This is where i get stuck. I have been
unsuccessful in writing an "IIF" statement to perform the calculations.
I'll
try and explain using attendance...If year is 2001, calculate percent
change
in attendance from 2000 to 2001...this would be repeated for each year.

Hope this helps...

Cecil


:

So the report has a *column* for each year?
And the column names are 2001, 2002, etc?

If so, you could add a text box to the Report Footer section, and give
it
these properties:
Control Source =Sum([2001])
Name txtSum2001

Then you could show the percentage difference in another text box with
properties like this:
Control Source =([txtSum2002] - [txtSum2001]) / [txtSum2001]
Format Percent

Once it's working, you probably want to refine it to:
=IIf([txtSum2001]=0, Null, ([txtSum2002] - [txtSum2001]) /
[txtSum2001])

Allen,

Thanks for your response, but unfortunately I have very little
experience
with VBA code outside usiing recommendations of others. I know my
way
around
Access 2003 pretty well and just cannot get over this hurdle. My
report
which
is based on a simple query looks similar to this:

2001 2002 2003 2004
Occasions 20 25 23 24
Attendance 224 125 122 200
Paper Sales 1000 1500 1250 1350

The data is located in the Year footer. I would like to calculate
the
percent change in each variable for each year. For example, the
calculation
for total yearly Attendance is =Sum([Persons Attending]). My
calculation
for
percent change of course comes to zero as i don't know the perform
this
type
of calculation whithin the footer... =(Sum([Persons
Attending])-Sum([Persons
Attending]))...

Your help is greatly appreciated.

:

If you just want to compare the value for one year with the value
in
the
previous record, and you are comfortable with VBA code, you could
do
it
like
this:

a) Declare a variable in the General Declarations section of the
report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to
remember
the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...

i have a report sectioned by year with number fields. I would like
to
calculate percent difference of the number fields for each year.
Click to expand...
Click to expand...
Click to expand...
 
Allen,

I have built crosstab quesries for each variable and year previously but was
hoping to avoid using the crosstab to build the report as i must reformat the
report when ever a new year added and the oldest year drops off the report. I
believe i understand where you are leading and will use crosstab to satisfy
my need. Thanks for all your help.

Allen Browne said:
Cecil, I don't think I can help you.

You don't have the years as columns, and you don't have them in the rows, so
I don't see anything you have that you can calculate a percentage of.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cecil said:
Allen,

Yes...but i don't know how to get to that point as I have only one field
for
year...([Year]). At each change in year the group starts over. I somehow
need to add a field that can calculate percent change as you describe
below
that recognizes the change in year.

Allen Browne said:
So you want something like this:
=([Yr2002] - [Yr2001]) / [Yr2001]

Hi Allen,

I'm am getting extremely close with the information provied from you
thus
far. Still a few more steps to go...The report has one cloum for the
year
and
because of the way i have the report configured the yearly totals are
horizontially across the page. So when you see 2001 2002 2003
it
is
actualy the ([Year]) field. This is where i get stuck. I have been
unsuccessful in writing an "IIF" statement to perform the calculations.
I'll
try and explain using attendance...If year is 2001, calculate percent
change
in attendance from 2000 to 2001...this would be repeated for each year.

Hope this helps...

Cecil


:

So the report has a *column* for each year?
And the column names are 2001, 2002, etc?

If so, you could add a text box to the Report Footer section, and give
it
these properties:
Control Source =Sum([2001])
Name txtSum2001

Then you could show the percentage difference in another text box with
properties like this:
Control Source =([txtSum2002] - [txtSum2001]) / [txtSum2001]
Format Percent

Once it's working, you probably want to refine it to:
=IIf([txtSum2001]=0, Null, ([txtSum2002] - [txtSum2001]) /
[txtSum2001])

Allen,

Thanks for your response, but unfortunately I have very little
experience
with VBA code outside usiing recommendations of others. I know my
way
around
Access 2003 pretty well and just cannot get over this hurdle. My
report
which
is based on a simple query looks similar to this:

2001 2002 2003 2004
Occasions 20 25 23 24
Attendance 224 125 122 200
Paper Sales 1000 1500 1250 1350

The data is located in the Year footer. I would like to calculate
the
percent change in each variable for each year. For example, the
calculation
for total yearly Attendance is =Sum([Persons Attending]). My
calculation
for
percent change of course comes to zero as i don't know the perform
this
type
of calculation whithin the footer... =(Sum([Persons
Attending])-Sum([Persons
Attending]))...

Your help is greatly appreciated.

:

If you just want to compare the value for one year with the value
in
the
previous record, and you are comfortable with VBA code, you could
do
it
like
this:

a) Declare a variable in the General Declarations section of the
report's
module (at the top, with the Option lines), e.g.:
Dim varAmount As Variant

b) Add some code to the Print event of the (Detail?) section to
remember
the
value, e.g.:
varAmount = Me.[Text22]

c) Add some code to the Format event of the section to assign the
calculation result to an unbound text box, e.g.:
Me.[txtDiff] = ...

i have a report sectioned by year with number fields. I would like
to
calculate percent difference of the number fields for each year.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top