>>My first query challenge was how to make a running total query. Michael Levy and Ed Pikman answered that one. Now I need to do the opposite. I am receiving data consisting of running totals of expended hours. I would like a query that extracts the amount expended each week. For example, if my data looks like this:
>>
>>
>>4/5/98 100
>>4/12/98 105
>>4/19/98 120
>>4/26/98 130
>>
>>
>>...I would like a query to return this:
>>
>>
>>4/12/98 5
>>4/19/98 15
>>4/26/98 10
>>
>>
>>If I have to, I will, once again, write a procedure to scan the table and write the cursor, but it would be nice to make it with a SQL SELECT.
>>
>>Thank you.
>
>Day of puzzles:). At least, this puzzle I know:
>select *,recno() as recno from table3 into cursor tmp1 order by date
>select numtot,recno+1 as recno from tmp1 into cursor tmp2
>select date,tmp1.numtot-tmp2.numtot as exphours from tmp1,tmp2 where tmp1.recno=tmp2.recno

What if "table3" is actually some cursor? I had the idea that RECNO() wasn't a good thing to use under that circumstance. I also wonder how it is affected by SET DELETED or whatever.

The basic idea of using RECNO() is to number the records in the query. Is there a convenient way to "autonumber" records in a query, i.e. assign 1,2,3 to subsequent records in your chosen order? Actually, I can think of a way. It involves SELECT *, 1 AS counter FROM mytable ORDER BY date, and then do Michael Levy's non-equi-self-join to make a running total of the "counter" field. If I do the joining right, I may be able to set the counter back to 1 at the beginning of a group. But I wonder if there is something simpler or faster.