How to get next row value in query rows (follow-up question)

[FinanceGuy2]

I’m trying to implement the recommended solution regarding “How to get next row…” that was posted July 12, 2008, but am having trouble with the “Autonum” part using Access 2003. In this case I am trying to put two rows of price data side by side so that I can calculate the daily percent change.
------------------------------------------ Solution------------------------------------------
"when you add an autonum field to the table you can create a query with:
Select autonumfield-1 as Oldkey... "
[ and later join this with the original table to get data side-by-side]

 ------------------------------------------ Table  ------------------------------------------
Price_date    Price    Dividend    RecNum
8/1/2008    $10.00    $1.00    {916E47CB-015A-466D-A2D0-...}
8/2/2008    $11.00    $1.50    {CDE5006B-5D05-4A35-B144-...}
8/3/2008    $12.00    $2.00    {AF81D70E-B18F-405F-9D87-...}

-------------------------------------------- SQL  -------------------------------------------
SELECT RecNum-1 AS RecNum2, Auto_Price_tbl.Price_date, Auto_Price_tbl.Price, Auto_Price_tbl.Dividend
FROM Auto_Price_tbl
WITH OWNERACCESS OPTION;

------------------------------------Query Output  ------------------------------------------
RecNum2    Price_date    Price    Dividend
#Error    8/1/2008    $10.00    $1.00
#Error    8/2/2008    $11.00    $1.50
#Error    8/3/2008    $12.00    $2.00

I have tried the query using variation of the Select statement:
“RecNum-1”, “RecNum – 1”, “[RecNum-1]” and “[RecNum]-1”
all to no result. There must be something about the solution that I don’t fully understand yet. Can you suggest where I might be going wrong?

[NeoPa]

I've searched for the thread you mention but was unable to find it.

Perhaps you could post a link - then we could start to look into this for you.

[hjozinovic]

I believe this is the old post:
Link

Also, I managed to get what I believe is the solution.
I noticed you're using RecNum as join field and trying to calculate RecNum-1. It looks to me RecNum is textual field and therefore can not be used in calculations.
What I did is this:
1. I created a table containing all the fields you listed and added a new field named ID (autonumber)
2. I created a Query1 based on that Table1 including all the fields and adding a new field: OldID:[ID]-1
3. I created second query based on table and query1.
These two are left joined Table1.ID->Query1.OldID
this query2 contains all the fields from Table and PriceDate field from query2

Here is how it looks:

[HTML]ID Table1.PriceDate Price Dividend RecNum Query1.PriceDate
1 01.08.2008 10,00 1,00 {916E47CB-015A-466D-A2D0-...} 02.08.2008
2 02.08.2008 11,00 2,00 {CDE5006B-5D05-4A35-B144-...} 03.08.2008
3 03.08.2008 12,00 2,00 {AF81D70E-B18F-405F-9D87-...}[/HTML]
hope this is what you wanted to have.

[NeoPa]

I believe this is the old post:
Link
...

Good for you H.

I'll wait to see how the OP fares with this :)

[FinanceGuy2]

Thanks guys! This did the trick. Although I was using "autonum" for the field, I had it set to field format of "replication ID" rather than "integer long". I thinks some MS help page suggested this and I foolishly followed that lead!

[NeoPa]

Foolish FinanceGuy!

That's like copying their example code. It never follows their own naming conventions even :D It is rarely any good for anything other than illustrating the point and giving an example in context. It does usually help - but is always worth taking with a pinch of salt.

PS. With my fairer hat on, this isn't entirely true in all cases. Some follow that trend, but I have found some reasonable code on MSDN and other MS provided resources.

[Stewart Ross]

Hi all. I'd be a little cautious about using [ID]-1 as a join to return the previous row along with the next, unless you are certain that the autonumbers are entirely contiguous throughout. If the autonumber has skipped one or more values the [ID]-1 join will fail to return certain rows. This is mentioned by Nico in his final post in the linked thread.

As you know, autonumbers are never re-used, so if a user simply cancels entry of the current record (or makes an entry then subsequently deletes the record) the gap between the autonumber for the previous record and the next in sequence will be at least 2, not 1.

I use the max of the joined ID where less than the current ID for such joins, as it works regardless of the gap in value between consecutive autonumbers.

Another point to consider is that it is implicit in this case that the rows will always be entered in ascending date order. This may well always be true for this application, but in other circumstances larger autonumber IDs might not correspond to later date entries - a user may enter data in descending date order, or not in order at all, in which case matching the previous ID does not correspond to matching the previous date.

-Stewart

[NeoPa]

Following on from Stewart's post, linking on the Max(subQ.PriceDate), WHERE subQ.PriceDate < Auto_Price_Tbl.PriceDate might be the safest and most appropriate way to link to the previous record in this case.