How to get best three rates on 10 in a report

[FRED]

I would like to print a report that will return the best
three freight rates for each destination I have to work
with. Each destination could have up to 20 quotations. I
want the report to return the three best per
destination.

BEST 2nd BEST 3rd BEST
Ex. Florida, ABC INC DEF INC XYZ INC
Denver DEF INC PNO INC XYZ INC
...

Here are table structure

Freight destination are in one table
Freight rate are in an other table with a link to
destination table.

Destination table Field
ID as primary key
City as text
state as text
Miles as integer

Rate table field
Carrier as text
Rates as integer
Destination ID as link to destination table

 

[Guest]

This is a good question for Queries section. Here it is:
You need a saved query, qryBestRates, like this:

SELECT A.DestinationID, A.Rate, A.CarrierID
FROM tblRates AS A
WHERE
A.CarrierID In
(
SELECT TOP 3 CarrierID
FROM tblRates AS B
WHERE B.DestinationID=A.DestinationID
ORDER BY DestinationID, Rate
)
ORDER BY A.DestinationID, A.Rate;

Table tblRates looks like this:
(CarrierID,Text;
DestinationID,LongInteger;
Rate,Currency)

Sample data and results (bellow data):
tblRates
--------------------------------
DestinationID Rate CarrierID
1 $10.00 DDD
1 $11.00 BBB
1 $12.00 GGG
1 $13.00 EEE
1 $14.00 AAA
1 $32.00 CCC
1 $34.00 FFF
2 $12.00 GGG
2 $15.00 AAA
2 $16.00 CCC
2 $16.00 BBB
2 $18.00 FFF
2 $43.00 EEE
2 $56.00 DDD
3 $10.00 AAA
3 $14.00 GGG
3 $18.00 CCC
3 $18.00 DDD
3 $24.00 BBB
3 $25.00 FFF
3 $56.00 EEE
4 $12.00 CCC
4 $17.00 GGG
4 $25.00 BBB
4 $27.00 EEE
4 $27.00 DDD
4 $37.00 AAA
4 $56.00 FFF
5 $12.00 AAA
5 $18.00 EEE
5 $18.00 BBB
5 $22.00 CCC
5 $28.00 GGG
5 $32.00 DDD
5 $33.00 FFF


Results:
qryBestRates
------------------
DestinationID Rate CarrierID
1 $10.00 DDD
1 $11.00 BBB
1 $12.00 GGG
2 $12.00 GGG
2 $15.00 AAA
2 $16.00 CCC
2 $16.00 BBB
3 $10.00 AAA
3 $14.00 GGG
3 $18.00 CCC
3 $18.00 DDD
4 $12.00 CCC
4 $17.00 GGG
4 $25.00 BBB
5 $12.00 AAA
5 $18.00 BBB
5 $18.00 EEE

Now, the results are not in cross tab format. The reason -
you do not get exactly 3 rates each time. Sometimes you
get 4-5 carriers that have 3 best rates (10,11,11,12) or
(10,11,11,11,11,12).

I suggest that you change the report layout, instead of
cross tab use simple tabular report, grouped by
destination ID.

 

[Guest]

Try these two queries:
a) best 3 carriers:
SELECT A.DestinationID, A.Rate, A.CarrierID
FROM tblRates AS A
WHERE (((A.CarrierID) In (SELECT TOP 3 CarrierID
FROM tblRates AS B
WHERE B.DestinationID=A.DestinationID
ORDER BY DestinationID, Rate
)))
ORDER BY A.DestinationID, A.Rate;


b) best 3 rates:
SELECT A.DestinationID, A.Rate, A.CarrierID
FROM tblRates AS A
WHERE (((A.Rate) In (SELECT TOP 3 Rate
FROM tblRates AS B
WHERE B.DestinationID=A.DestinationID
ORDER BY DestinationID, Rate
)))
ORDER BY A.DestinationID, A.Rate;