I finally got the answer to the second highest value in a table.
I first queried for the max amount of the discount, then I queried for
the max amount of the discount where the discount from the table was
not equal to the max discount of the first query.
Below is the first query:
SELECT DISTINCT tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State], Max(tblLatestVersionOfRates.Discount) AS MaxOfDiscount
FROM tblLatestVersionOfRates
GROUP BY tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State]
HAVING (((tblLatestVersionOfRates.[Origin City])="ashland city"));
The following is the second query (SECOND HIGHEST NUMBER):
SELECT tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State], Max(tblLatestVersionOfRates.Discount) AS MaxOfDiscount1
FROM tblLatestVersionOfRates INNER JOIN qryMaxDiscountByLane ON
(tblLatestVersionOfRates.Discount <>
qryMaxDiscountByLane.MaxOfDiscount) AND (tblLatestVersionOfRates.[Dest
State] = qryMaxDiscountByLane.[Dest State]) AND
(tblLatestVersionOfRates.[Origin State] = qryMaxDiscountByLane.[Origin
State]) AND (tblLatestVersionOfRates.[Origin City] =
qryMaxDiscountByLane.[Origin City])
WHERE (((tblLatestVersionOfRates.[Origin City])="Ashland City"))
GROUP BY tblLatestVersionOfRates.[Origin City],
tblLatestVersionOfRates.[Origin State], tblLatestVersionOfRates.[Dest
State];
The following is a query that combines the first query with the
original table to give me the information that I need as output. This
is the maximum discount for the carrier by state originating from and
destined to:
SELECT tblLatestVersionOfRates.[Origin City],
qryMaxDiscountByLane.[Origin City], tblLatestVersionOfRates.[Origin
State], qryMaxDiscountByLane.[Origin State],
tblLatestVersionOfRates.[Dest State], qryMaxDiscountByLane.[Dest
State], tblLatestVersionOfRates.Discount,
qryMaxDiscountByLane.MaxOfDiscount, tblLatestVersionOfRates.SCAC
FROM tblLatestVersionOfRates INNER JOIN qryMaxDiscountByLane ON
(tblLatestVersionOfRates.Discount = qryMaxDiscountByLane.MaxOfDiscount)
AND (tblLatestVersionOfRates.[Dest State] = qryMaxDiscountByLane.[Dest
State]) AND (tblLatestVersionOfRates.[Origin State] =
qryMaxDiscountByLane.[Origin State]) AND
(tblLatestVersionOfRates.[Origin City] = qryMaxDiscountByLane.[Origin
City]);
Now for the SECOND HIGHEST Discount with the fields I wanted to group.
SELECT tblLatestVersionOfRates.[Origin City],
qrySecondHighestLTLRates.[Origin City], tblLatestVersionOfRates.[Origin
State], qrySecondHighestLTLRates.[Origin State],
tblLatestVersionOfRates.[Dest State], qrySecondHighestLTLRates.[Dest
State], tblLatestVersionOfRates.Discount,
qrySecondHighestLTLRates.MaxOfDiscount1, tblLatestVersionOfRates.SCAC
FROM tblLatestVersionOfRates INNER JOIN qrySecondHighestLTLRates ON
(tblLatestVersionOfRates.Discount =
qrySecondHighestLTLRates.MaxOfDiscount1) AND
(tblLatestVersionOfRates.[Dest State] = qrySecondHighestLTLRates.[Dest
State]) AND (tblLatestVersionOfRates.[Origin State] =
qrySecondHighestLTLRates.[Origin State]) AND
(tblLatestVersionOfRates.[Origin City] =
qrySecondHighestLTLRates.[Origin City])
ORDER BY tblLatestVersionOfRates.[Dest State];
Thank you very much for your help. I wanted to post in case it could
help someone else.
Robert said:
There may be a better way but this is what I'd do:
Make a query selecting only the Top 1 (i.e. max) of your discount rates.
Then use that query and your original table in a "Find Unmatched Query" (use
the wizard if you need to) and grab the max of what that query returns.
Hope that helps
I have a table called tblRates. I need to find out the second highest
pricing discount into a state for each state out of four different
states. The table fields are:
Carrier_Name
Origin_State
Dest_State
Carrier_Discount
So the data looks like this:
Carrier_Name Origin_State Dest_State Carrier_Discount
Roadway TN AL 62
Yellow TN AL 53
ABF TN AL 57
SAIA TN AL 59
Roadway TN AR 61
ABF TN AR 57
What I need is the following showing the carrier with the second
highest discount into the state:
Carrier_Name Origin_State Dest_State Carrier_Discount
ABF TN AL 57
ABF TN AR 57
I can get the highest discount using Select
max(tblRates.[Carrier_Discount]) but can not get the second highest.
I have experimented with querying the table against the table, but can
not figure out.
I have tried Select Distinct Top 2
.[field] but can not then
group information to include carrier name, origin state, or destination
state. I only get the top two discounts for the entire table.
Any assistance would be greatly appreciated. Thank you in advance.
Robert