Immediately previous record value by query

Posted on 2006-04-06
Last Modified: 2011-09-13

Hi there!

Let us say I have a table with field "Name X" with the following values:


I want to create a query with 2 fields, one with the same "NameX" values and the other with exactly the same correspondent values but... with one "step below" (sorry for my English).  

I mean I get the query as:

SameAsNameX     NameXOneStepBelow
   2                                 0 (to be put)
   1                                 2
   5                                 1
  120                               5
   4                               120
  500                               4

To make myself clearer: I have to calculate a % variation, therefore I need to calculate:

  X_n - X_(n-1)  divided by X_(n-1)  and I have difficulty in findind the difference between consecutive values dynamically.

What I want?  The expression to be used in the created field "NameXOneStepBelow" to get the values as explained.

Thanks a lot,

Question by:fskilnik
    LVL 58

    Accepted Solution


    You will need an ID field to determine the sort order. As such, a table has no sort order, so that there is no "previous" record. There is, however, a "previous entry" in a unique index.

    You are talking about "consecutive dates". If you really want that -- the day before -- and if that field has a unique index you can use this:

        FROM tblYourTableName As CURRENT
        LEFT JOIN tblYourTableName As PREVIOUS
        ON CURRENT.datTheDateField = PREVIOUS.datTheDateField+1
        ORDER BY CURRENT.datTheDateField

    If you really want any previous date, e.g. last Fridays value to compare with Monday's, you need a subquery:

        SELECT *, (
            Select Top 1 lngTheValueField
            From tblYourTableName As PREV
            Where datTheDateField < tblYourTableName.theDateField
            Order By datTheDateField DESC
            ) As lngValueOfPreviousDate
        FROM tblYourTableName

    Barring any spelling or punctuation errors, both should work.

    Good luck!

    Author Comment


    Hello harfang,

    Thanks for the reply.

    I need "the second option" but your suggestion didn´t work or, probably, I could not make it work in my situation.

    To let things easier, let me show you what I have already been able to do closely related to that and please adapt this to our problem, ok?!

    If I have:  (ID´s not mentioned but yes, they are there, sure)

    Date                       qty
    01/jan/06               15
    01/feb/06               25
    01/mar/06              40
    01/apr/06               35

    I can accumulate the values till the date "at that moment" to get

    Date                       qty     AccumQty
    01/jan/06               15           15
    01/feb/06               25           40
    01/mar/06              40           80
    01/apr/06               35           115

    using the expression:

    AccumQty: (Select SUM(tblBuys.qty)  FROM tblBuys
    Where ( <=

    where I need two references, one (tblBuys) to look for the values and two (qryBuys) to pre-consider which date will be, till that moment, the one to be considered as a reference. Therefore I will only get the qty´s from tblBuys while these qty´s are from dates <= the date considered at the moment.

    I guess you understood it all because the code you gave me "proves" that you are better than me structuring all this.

    My idea was to use the "TOP 1" suggestion from you, but if I put

    NewName: (Select TOP 1 (tblBuys.qty)  FROM tblBuys
    Where ( <=

    I would get

    Date                       qty     NewName
    01/jan/06               15           15
    01/feb/06               25           15
    01/mar/06              40           15
    01/apr/06               35           15

    because the TOP1 thing could NOT understand that he must get the TOP 1 but, in the consecutive records, this TOP1 should "move"... got the picture?

    Thanks a lot,


    Author Comment


    Hi there, harfang!  Good news!!

    I could do it with

    NameXOneStepBelow: (Select LAST (TheValueField)  FROM tblREFERENCE
    Where (tblREFERENCE.valueDate <  tblRUNNING.valueDate))

    But, if you don´t mind, could you please explain (or give a link) the rationale of your 2nd code? I guess this subquery stuff is powerful, but I don´t know how to read it properly.

    Thanks a lot.


    Obs.: I was curious about the (°v°) thing, I went to your profile and saw the nice drawing!! By the way, your expertise is really impressive, congrats!!!

    LVL 58

    Expert Comment


    You forgot one line from the suggestion.

    NewName: (Select TOP 1 (tblBuys.qty)  FROM tblBuys
    Where ( <=
    Order By DESC)                        <----- this one ;)

    Without the order clause, it's just the "top 1" record in any arbitrary order, and you got the value for 1st Jan.
    With the order clause, you sepcifically ask for the "last" date's qty field.

    You had things working using Last(tblBuys.qty) as a substitute for "Top 1 / Order By". At first sight, it looks OK, and it might even work well during your test phase. However, there is a bug lurking under that stone.

    Last() says, "just give me the last value you encounter". Even if  you add an order clause, it will be applied after the query has gathered records. As long as you enter your data in sequence, or if the date is the key field and you just compaced the database, all will be well. If not, you might get another record, i.e. "the last one entered into the table" instead of "the last one chronologically".

    Hope this helps!

    Author Comment


    Perfect! Simply Perfect, harfang.

    Although the data will be entered in sequence (by the nature of the data themselves) , your comments and expertise are most welcomed.

    I think you are THE person to ask for the BEST "advanced Access" book in the market, please!  I would like to learn about VBA for Access, advanced queries (as the one you suggested) , etc.  Could you please name the one(s) you like best?

    I know this is not related to the original question but, as I said, YOUR recommendation would be very important to me.

    Thanks in advance and all the best,

    LVL 58

    Expert Comment

    Thanks Fábio, that is very flatering.

    But sadly, I really can't recommend any book. The last one I used was about Access 2. Not 2k, mind you, version 2. When I got version 95 and 97, I just started reading the help files. Do you know that the printed version of the Access 2000 help file has 1500 pages? I've seen it in a store. I much prefer the contextual hypertext version I get on screen.

    Let's take queries. If you surf the JetSQL reference, you'll find many examples of queries. Some are "air code" but simple and easy to understand, but most are based on the tables in the Northwind database. So you can basically copy/paste them to try them out immediately. Beats any book.

    Then there is the time spent with little challenges: how would I store information to create family trees? can I have alternating color lines in a report? or whatever crosses your mind. I did a lot of that.

    And let's not forget, EE is a great place to learn. Look at the list of open questions, if you find one interesting, comment or subscribe and see it evolve. Also, create sample databases to try out the things you are reading about.

    Enough rambling.

    What I usually answer to book recommendation questions is this: go to a LARGE CT bookstore, and spend one afternoon reading at least 20 pages of each book on the given topic. At some point, you'll be reading on. That's the book for you, and it's not likely to be the best book for me or your neighbour.

    Thanks again for the compliments and sorry I have no recommendations for you.

    Author Comment


    Your suggestions are mature and to-the-point, harfang. The help files, Northwind associated stuff and EE options are really better choices, I guess. I should have thought about them all, by the way (laugh). They are more flexible, probably more complex and reader-demanding and also strictly goal-oriented... not to mention the inherent limitations of a book itself!

    So, in short, you gave me more than the recommendations I asked. The same is true to the answer to this posting.

    I really hope I have other opportunities to learn from your expertise and, whenever I have time, I will try to study YOUR EE solutions, for sure.  :)

    Thanks a lot. It was a pleasure (and very instructive) to "meet you" here.

    Best regards,

    LVL 58

    Expert Comment

    The pleasure is all mine. It's nice to have some real feedback and conversation, too many users here just grab the solution and run...
    Before I go, here is some more study material, this time for tricks with forms:
    (just because I forgot to mention the MS knowlege base before...)


    Author Comment


    Great, harfang!  Thanks for the link and for all your kindness.

    > It's nice to have some real feedback and conversation, too many users here just grab the solution and run...

    I totally agree. We are first human beings, then, only then, EE question makers and solvers! As far as makers are concern, that´s even worse because we should show some gratitude... some respect to the solvers knowledge, experience and good-will in helping us.

    Cheers and best wishes!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now