Immediately previous record value by query


Hi there!

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

NameX
   2
   1
   5
  120
   4
  500

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,
fskilnik.


 
fskilnikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
Hello,

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:

    SELECT CURRENT.*, PREVIOUS.*
    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!
(°v°)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fskilnikAuthor Commented:

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
etc

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,
fskilnik


0
fskilnikAuthor Commented:

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.

Regards,
fskilnik.

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

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

harfangCommented:
Hello,

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!
(°v°)
0
fskilnikAuthor Commented:

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,
Fábio.




0
harfangCommented:
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.
(°v°)
0
fskilnikAuthor Commented:

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,
Fábio.


0
harfangCommented:
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:
http://support.microsoft.com/kb/233324/EN-US/
(just because I forgot to mention the MS knowlege base before...)

Cheers!
(°v°)
0
fskilnikAuthor Commented:

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!
Fábio.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.