By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

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

P: 2
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]
Expand|Select|Wrap|Line Numbers
  1.  ------------------------------------------ Table  ------------------------------------------
  2. Price_date    Price    Dividend    RecNum
  3. 8/1/2008    $10.00    $1.00    {916E47CB-015A-466D-A2D0-...}
  4. 8/2/2008    $11.00    $1.50    {CDE5006B-5D05-4A35-B144-...}
  5. 8/3/2008    $12.00    $2.00    {AF81D70E-B18F-405F-9D87-...}
Expand|Select|Wrap|Line Numbers
  1. -------------------------------------------- SQL  -------------------------------------------
  2. SELECT RecNum-1 AS RecNum2, Auto_Price_tbl.Price_date, Auto_Price_tbl.Price, Auto_Price_tbl.Dividend
  3. FROM Auto_Price_tbl
  4. WITH OWNERACCESS OPTION;
Expand|Select|Wrap|Line Numbers
  1. ------------------------------------Query Output  ------------------------------------------
  2. RecNum2    Price_date    Price    Dividend
  3. #Error    8/1/2008    $10.00    $1.00
  4. #Error    8/2/2008    $11.00    $1.50
  5. #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?
Aug 5 '08 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,661
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.
Aug 7 '08 #2

100+
P: 167
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.
Aug 8 '08 #3

NeoPa
Expert Mod 15k+
P: 31,661
I believe this is the old post:
Link
...
Good for you H.

I'll wait to see how the OP fares with this :)
Aug 8 '08 #4

P: 2
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!
Aug 22 '08 #5

NeoPa
Expert Mod 15k+
P: 31,661
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.
Aug 23 '08 #6

Expert Mod 2.5K+
P: 2,545
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
Aug 23 '08 #7

NeoPa
Expert Mod 15k+
P: 31,661
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.
Aug 24 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.