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

[Bas Cost Budde]

Tony Williams wrote:

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.

It is not very straightforward in a database system, because tables are
unordered in principle.

All following SQL is air code.

You can have a query that finds the next date using two steps:

(1) have the table twice in the first query, but for the second date
enter criteria >firsttable.txt month
In SQL it could look like this:

SELECT t1.txtMonth AS firstDate, t2.txtMonth AS laterDate
FROM yourtable AS t1, yourtable AS t2
WHERE t2.txtMonth>t1. txtMonth

Save this as qTriangle (if you visualize the result set as t1 in rows,
t2 in columns, you may see why I name it thus)

(2) create a query on this query, grouping by first date, Min-ing second
date:

SELECT firstDate AS theDate, Min(laterDate) AS nextDate
FROM qTriangle
GROUP BY firstDate

Save this as qDatePair

This query you can use as 'pole' to connect the original table to, to
find the corresponding txtValues:

SELECT t1.txtValue AS theValue, t2.txtValue AS valueNextMonth, theDate
FROM (qDatePair INNER JOIN yourtable AS t1 on
t1.txtMonth=qDa tePair.theDate) INNER JOIN yourtable AS t2 on
qDatePair.nextD ate=t2.txtMonth

This query can be used in the report, making the calculation you ask for
a trivial action. I hope.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Tony Williams wrote:

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.

At least you clearly state 'repost'!

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Tony Williams]

Thanks Bas I'm a newbie so that went over my head a bit, I'm going to need
time to study that. Is there any site that shows examples of this?
Tony
"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cp******** **@news2.solcon .nl...

Tony Williams wrote:

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.

It is not very straightforward in a database system, because tables are
unordered in principle.

All following SQL is air code.

You can have a query that finds the next date using two steps:

(1) have the table twice in the first query, but for the second date
enter criteria >firsttable.txt month
In SQL it could look like this:

SELECT t1.txtMonth AS firstDate, t2.txtMonth AS laterDate
FROM yourtable AS t1, yourtable AS t2
WHERE t2.txtMonth>t1. txtMonth

Save this as qTriangle (if you visualize the result set as t1 in rows,
t2 in columns, you may see why I name it thus)

(2) create a query on this query, grouping by first date, Min-ing second
date:

SELECT firstDate AS theDate, Min(laterDate) AS nextDate
FROM qTriangle
GROUP BY firstDate

Save this as qDatePair

This query you can use as 'pole' to connect the original table to, to
find the corresponding txtValues:

SELECT t1.txtValue AS theValue, t2.txtValue AS valueNextMonth, theDate
FROM (qDatePair INNER JOIN yourtable AS t1 on
t1.txtMonth=qDa tePair.theDate) INNER JOIN yourtable AS t2 on
qDatePair.nextD ate=t2.txtMonth

This query can be used in the report, making the calculation you ask for
a trivial action. I hope.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Tony Williams wrote:

Thanks Bas I'm a newbie so that went over my head a bit, I'm going to need
time to study that. Is there any site that shows examples of this?

Not yet. I hoped you supplied the real names, in that case you can cut
the SQL and paste it in queries. It would work.

To create an example would take me some time that I don't have before
next week. If you can wait that long, please do; if not, you can mail me
for a consult.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Tony Williams]

Thanks Bas, I'll have a go. Actually there are about 8 values that I need to
do this calculation on in the same report. Can I assume that I just repeat
the code for each value?
Thanks
Tony
"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cp******** **@news2.solcon .nl...

Tony Williams wrote:

Thanks Bas I'm a newbie so that went over my head a bit, I'm going to need time to study that. Is there any site that shows examples of this?

Not yet. I hoped you supplied the real names, in that case you can cut
the SQL and paste it in queries. It would work.

To create an example would take me some time that I don't have before
next week. If you can wait that long, please do; if not, you can mail me
for a consult.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Tony Williams wrote:

Thanks Bas, I'll have a go. Actually there are about 8 values that I need to
do this calculation on in the same report. Can I assume that I just repeat
the code for each value?

If for all values you want to -er- progress on the same date, no, you
can have all values in the last query and do the report at once.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea