Nested query

[cmassari]

Hello and thanks for reading my question.

The problem I have is that I want to query a table ad join it to
himself in a very particular way.

this is the table I have:

FUNDS
fund_name date value
-----------------------------------------------
fund_a 1/1/2001 30
fund_a 1/2/2001 32
fund_a 1/5/2001 35
fund_b 1/1/2001 17
fund_c 1/1/2001 23


Basically, what I need is to have an extra column in the result that
shows the value for the investement funds the previos day (not
necessary the PREVIOUS because samples might be missing but the newest
old value possible for the record date)

the result should look like this:

fund_name date value prev_value
------------------------------------------------------------------------
fund_a 1/1/2001 30 null
fund_a 1/2/2001 32 30
fund_a 1/5/2001 35 32
fund_b 1/1/2001 17 null
fund_c 1/1/2001 23 null

Im having troubles to build this query in ms Access and I will really
appreciate if you can help me with it.

Regards from Argentina!

Cristian.

 

[Allen Browne]

Use a subquery to get the value for the matching fund from the most recent
date.

If subqueries are new, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

You will need to ensure that a fund doesn't have multiple values for the
same date. Also the query results will be read-only.

If you need an updatable query, you could use this lookup function instead:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html

 

[cmassari]

Thank you very much for your help Allen!!!

Your site is on my favourite list now ;-)

Cristian.