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

Expression building in query using a current record and the previous record

P: 2
I need to build a query on one table using a field in a current record and the same field in the previous record. Essentially, I need to know the difference between the value in the current record field and the value in the the previous record field. Is there a way to do this in a query?
Sep 17 '10 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
Are the current and previous records that you need in a recordset? If so you can do something like this (where rs is the recordset holding the data in question:

Expand|Select|Wrap|Line Numbers
  1. Dim value
  2.  
  3. value = rs("fieldname")
  4. rs.MovePrevious
  5. value = value - rs("fieldname")

You could then use value for whatever purpose you have in mind. Just be aware of the type of recordset that you're using. The MovePrevious method won't work on a recordset type that allows forward-only movement.

Pat
Sep 17 '10 #2

P: 2
Thanks Pat. I was hoping to get away without doing it in code! Have done as you suggested and it is working fine. The data was needed to build a chart to display daily power output of our hydro electric plant - working well now, thanks.
Sep 17 '10 #3

patjones
Expert 100+
P: 931
Hi Ross,

I'm really glad that I was able to help. It was a code-based solution, but at least the code isn't that complicated. And by the way, I completely support alternative means of producing energy.

If there is anything else I can do to assist in your database work let me know or just put up another posting. The experts here are extremely helpful.

Pat
Sep 17 '10 #4

Post your reply

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