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.costbudde@heuvelqop.nl> wrote in message
news:cppeco$f08$1@news2.solcon.nl...[color=blue]
> Tony Williams wrote:[color=green]
> > SORRY I know we shouldn't do this but I'm desperate for an answer to[/color][/color]
this[color=blue][color=green]
> > and the previous post didn't seem to get a response.
> >
> > I have a table with two fields, txtvalue (a number field) and txtmonth[/color][/color]
( a[color=blue][color=green]
> > 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[/color][/color]
value[color=blue][color=green]
> > of txtvalue in another value of txtmonth and the percentage increase .[/color][/color]
For[color=blue][color=green]
> > example if I have the value 1000 in 30/03/03 and the value 1100 in[/color][/color]
30/03/04[color=blue][color=green]
> > How do I calculate the difference as 100 and the increase as 10%. I want[/color][/color]
to[color=blue][color=green]
> > do this in a report.
> >
> > I've tried using an append query to create a dummy table but couldn't[/color][/color]
get[color=blue][color=green]
> > that to work at all. Anyone any ideas? This seems such a simple[/color][/color]
calculation[color=blue][color=green]
> > that would be easy in Excel but I want to produce an Access report.[/color]
>
> 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[/color]
