Calculating the difference in two values

[Tony Williams]

SORRY I know we shouldn't do this but I'm desperate for an answer to this
and the previous post didn't seem to get a response.

I have a table with two fields, txtvalue (a number field) and txtmonth ( a
date/time field). I want to create a report that shows the difference in
value between the value in txtvalue in one value of txtmonth and the value
of txtvalue in another value of txtmonth and the percentage increase . For
example if I have the value 1000 in 30/03/03 and the value 1100 in 30/03/04
How do I calculate the difference as 100 and the increase as 10%. I want to
do this in a report.

I've tried using an append query to create a dummy table but couldn't get
that to work at all. Anyone any ideas? This seems such a simple calculation
that would be easy in Excel but I want to produce an Access report.

TIA
Tony Williams

 

[Jeff Boyce]

Tony

I'm looking for a bit more information here. From your description, the
only difference between the two rows is the txtvalue field (both have the
same date in txtmonth). How do you (or Access) know which one to start with
in calculating "% change"?

 

[Marshall Barton]

SORRY I know we shouldn't do this but I'm desperate for an answer to this
and the previous post didn't seem to get a response.

I have a table with two fields, txtvalue (a number field) and txtmonth ( a
date/time field). I want to create a report that shows the difference in
value between the value in txtvalue in one value of txtmonth and the value
of txtvalue in another value of txtmonth and the percentage increase . For
example if I have the value 1000 in 30/03/03 and the value 1100 in 30/03/04
How do I calculate the difference as 100 and the increase as 10%. I want to
do this in a report.

I've tried using an append query to create a dummy table but couldn't get
that to work at all. Anyone any ideas? This seems such a simple calculation
that would be easy in Excel but I want to produce an Access report.

Sorry Tony, but your question is kind of ambiguous and, as
stated, can't really be answered. A field/control in a
query/report can only have one value at a time, so I don't
know what you mean by " the value in txtvalue in one value
of txtmonth and the value of txtvalue in another value of
txtmonth".

The only thing that I think might make sense of that is if
you want to compare the value in the current record to the
value in the record with the most recent date prior to the
date in the current record. If that's what you want, then
you could try using something along the lines of this:

DLookup("txtvalue", "thetable", "txtmonth = " &
DMax("txtmonth", "thetable", "txtmonth < #" & txtmonth &
"#")

 

[Tony Williams]

Sorry Marsh. Let me have another go. At each quarter the user gets a set of
statistics. The quarter is identified by txtmonth. There may be values for
upto say 8 fields so txtvalue1, txtvalue2,txtvalue3 etc all number fields
are input into the table via a form. If the month is say September 2004 they
then want to compare the values they have received in that month with the
values they received a year ago in September 2003 and in the previous
quarter being June 2004. Having compared the values they want to calculate
the %difference. I think they will need a form to key in which dates they
want to compare but I think I can do that, it's how do I code the comparison
to show on a report? Is that any better?
TIA
Tony

 

[Marshall Barton]

Sorry Marsh. Let me have another go. At each quarter the user gets a set of
statistics. The quarter is identified by txtmonth. There may be values for
upto say 8 fields so txtvalue1, txtvalue2,txtvalue3 etc all number fields
are input into the table via a form. If the month is say September 2004 they
then want to compare the values they have received in that month with the
values they received a year ago in September 2003 and in the previous
quarter being June 2004. Having compared the values they want to calculate
the %difference. I think they will need a form to key in which dates they
want to compare but I think I can do that, it's how do I code the comparison
to show on a report?

A little better, but you're still two steps ahead of
yourself. Comparing the values is easy, the dificult part
of this is identifying and retrieving the records with the
values you want to compare.

You say the txtMonth field in the table is a Date/Time field
and if you have a text box named txtPrevQtr on the form
where you enter a date such as Sept 2003 (which will be the
date 9/1/03), then you can retrieve the value using a
textbox expression:

=DLookup("txtvalue", "thetable", "txtmonth = #" &
txtPrevQtr & "#")

If your dates are not tht recise, you could search the table
for the corresponding value based on the quarter the date
field falls into:

=DLookup("txtvalue", "thetable", "Format(txtmonth,
""yyyyq"") =" &Format(txtPrevQtr,"yyyyq"))

Once you have that part sorted out, let's say the text box
is named txtPrevQtrValue, then the calculations are simply:

txtDifference
=txtMonth - txtPrevQtrValue

txtPctChange
=IIf(Nz(txtPrevQtrValue, 0) = 0, 0, (txtMonth -
txtPrevQtrValue) / txtPrevQtrValue)

 

[Tony Williams]

Thanks Marsh. Sorry I didn't acknowledge your reply earlier but I'm in the
UK and at 21.15 last night I decided it was time to have a break. Anyway
it's now 9.45am and refreshed after a nights sleep.

I'll try and work my way through your answer.

I'm a bit confused over this part
txtDifference =txtMonth - txtPrevQtrValue
should this be
txtDifference =txtvalue - txtPrevQtrValue?
As I understand it here we're subtracting two values whereas txtmonth is a
date?
The same applies in the % calculation or am I not understanding what's going
on here?

I really appreciate your help
Tony

 

[Marshall Barton]

Yes, of course, both of those should be txtValue, not
txtMonth.

I guess I needed some sleep too ;-)

Be careful here, when I wrote:

txtDifference
=txtValue - txtPrevQtrValue

I meant the the text box named txtDifference would use the
control source expression =txtMonth - txtPrevQtrValue