Immediately previous record value by query

Posted on 2006-04-06
Medium Priority
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
  • 5
  • 4
LVL 58

Accepted Solution

harfang earned 600 total points
ID: 16395436

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

ID: 16396258

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 (tblBuys.date <=  qryBuys1.date))

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 (tblBuys.date <=  qryBuys1.date))

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

ID: 16396687

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!!!

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 58

Expert Comment

ID: 16398158

You forgot one line from the suggestion.

NewName: (Select TOP 1 (tblBuys.qty)  FROM tblBuys
Where (tblBuys.date <=  qryBuys1.date)
Order By tblBuys.date 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

ID: 16400088

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

ID: 16400419
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

ID: 16402398

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

ID: 16402783
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

ID: 16403677

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

593 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