Dividing 1 Field by the 1 above

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello
can any tell me how i write a query to divide the value for 23/06 by the
value for the 22/06 i.e 1.58406/1.59262 and create a new column called Return
like below?

Date Value Return
22/06/2004 1.59262
23/06/2004 1.58406 0.9946

Basically I'm trying to calculate the reutrns for an index. Please help!!
Many thanks
 
Hi
Thanks for that.
Unfortunately my table includes a number of indices so I have multiple
records for the same date so I cant use the ID column. Any other ideas would
be greatly appreciated. Many thanks
 
So if you strike mulitple records for the same date, how do you decide which
one you want?

If it is the one with the highest ID for the date, you could use this
extended replacement for DLookup():
http://allenbrowne.com/ser-42.html
This ELookup() function allows you to specify the sort order of the records,
so you can indicate which one you want returned.
 
You can use criteria based on all 4 fields if you need to, and also specify
several fields for the OrderClause argument.
 
I am quite to access so please forgive my lack of knowledge. I don't know how
to use the OrderClause statement and the MSHelp function isn't too great.

The problem is that the data in the 4 field is attributed to a different
property.
eg below:
Date Market CurveProperty Property Bucket Value Returns
21-Jun-04 12 1 3 1 402.36619
21-Jun-04 12 1 3 2 475.09934
21-Jun-04 12 1 3 3 536.85344
I have created a query that does this and brings out the right data in date
order with identical properties, but IDs are not linear. The problem is now i
just want to divide one date by a lag of a day and its driving me mad!
Appreciate all your help so far and hoping you can solve this. =)
thanks again
 
Alex, the function will work as described in the article, and if DLookup()
is inadquate, ELookup() will let you choose the particular one you need.

That's probably as far as I can take you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula in Access Query to genrate numbers 1
Advanced problem using auto-number and datediff to find readmissio 1
Help needed with getting data out a running sum table. 1
how do i count consecutive records with a field value >=1 12
OT: Selective HP drivers (apologies for length) 7
Between two dates and times 1
Date Brackets as an IIf statement 3
Amazon are rubbish 14

Back
Top