retrieve effective rates

G

Guest

I have a table with three columns: Type, Year, Rate. A new record is added
for a given type only when the rate is updated. How can I select, for each
type, the rate that is in effect for a specified year? I.E. For each type,
retrieve the most recent record that is less than or equal to a specified
year, which is retrieved from another table.

Example:
Rec# Type Year Rate
1 1 2004 20
2 1 2006 30
3 2 2004 30
4 2 2005 25
5 2 2007 40

Specified Year Records Returned
2004 1, 3
2005 1, 4
2006 2, 4
2007 2, 5

Any suggestions would be greatly appreciated.

....Larry
 
A

Allen Browne

Larry, there are several issues to address here.

The first is how to get the most recent rate for each combination of Year
and Type, even if there is no specific record for the Year + Type.

The missing years must come from somewhere, so you will need a table of
Years (one record for every year you need to consider.) Presumably you
already have a table of Types. Create a query using both tables (Years and
Types.) There should be no record joining the 2 tables in the upper pane of
query design. This is known as a Cartesian Product, and yields every
possible combination (i.e. every type for every year.) Save this query.

Now you want to get the rate in force at that time. A subquery might be the
easiest way to do that. If subqueries are new, see:
http://allenbrowne.com/subquery-01.html
It would also be possible (through less efficient) to use ELookup() if you
prefer:
http://allenbrowne.com/ser-42.html
For a discussion of the issues involved, Tom Ellision's article "Lookup in a
range of values in a query" might help:
http://allenbrowne.com/ser-58.html

Finally, I'm not sure if you actually want to concatenate the values in the
format shown by your last list, but if so you need the technique described
here:
http://www.mvps.org/access/modules/mdl0004.htm

It will take some effort to achieve, but hopefully that provides a useful
direction to get you started.
 
G

Guest

Thanks Allen, but I'm not sure how the cross-product is going to help me.
When I cross the sample table below with all possible years (2004, 2005,
2006, 2007), I get the following result.

Type Year Rate YearAll
1 2004 20 2004
1 2004 20 2005
1 2004 20 2006
1 2004 20 2007
1 2006 30 2004
1 2006 30 2005
1 2006 30 2006
1 2006 30 2007
etc.

....Larry
 
G

Guest

I think I have a solution. I added a column to my original table, containing
a unique record number. I then created a query which selects Max(RecNum)
where Year <= [desired year], grouped by Type. I then created another query
where I joined this query to the original table, on RecNum. This seems to
work.

Thanks for your help, Allen.
 

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

Top