complicated query building help please

[Susan]

I have a rather large database containing rates from and to different
locations.

I am trying to write a query that will give me the 'most' current rate for
each lane, and there are hundreds of lanes and combinations, and rates are
updated daily, but have an expiry date. I need not only the current rate,
but to be able to see which lanes are expiring or shortly expiring

A-b - exp 05/06
a-e - exp 04/06

How do I write a query that will only return one of each combination without
having to specify every single combination?

 

[Douglas J. Steele]

I doubt anyone will be able to help you without some details of what the
tables involved look like.

 

[Susan]

It is one table which has the columns To:, From:, Carrier, Rate, Expiry. I
need to be able to show the current record for each combination of To&From.

 

[KARL DEWEY]

Try this using two queries.

SELECT FREIGHT.TO, FREIGHT.FROM, Max(FREIGHT.EXPIRY) AS MaxOfEXPIRY
FROM FREIGHT
GROUP BY FREIGHT.TO, FREIGHT.FROM;


SELECT FREIGHT.TO, FREIGHT.FROM, FREIGHT.CARRIER, FREIGHT.RATE, FREIGHT.EXPIRY
FROM FREIGHT INNER JOIN LastRate ON (FREIGHT.EXPIRY = LastRate.MaxOfEXPIRY)
AND (FREIGHT.FROM = LastRate.FROM) AND (FREIGHT.TO = LastRate.TO);