435,361 Members | 3,185 Online
Need help? Post your question and get tips & solutions from a community of 435,361 IT Pros & Developers. It's quick & easy.

# Repost- Calculating the difference in two values

 P: n/a 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 Nov 13 '05 #1
6 Replies

 P: n/a 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.txtmonth 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=qDatePair.theDate) INNER JOIN yourtable AS t2 on qDatePair.nextDate=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 Nov 13 '05 #2

 P: n/a 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 Nov 13 '05 #3

 P: n/a 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" 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.txtmonth 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=qDatePair.theDate) INNER JOIN yourtable AS t2 on qDatePair.nextDate=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 Nov 13 '05 #4

 P: n/a 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 Nov 13 '05 #5

 P: n/a 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" 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 Nov 13 '05 #6

 P: n/a 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 Nov 13 '05 #7

### This discussion thread is closed

Replies have been disabled for this discussion.