Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to select second cheapest, third cheapest, etc. Access 2007

Posted on 2011-03-01
Medium Priority
431 Views
Hello, do do I pick a specific price point in Access 2007? For example, we are in the long distance telephone industry have have a pool of carriers that service area codes. I would like a query that says "show the third cheapest price for Area Code 562.

In this case the result would be: 562,PBELL,0.09

I'm only familiar with the First and Max expressions, so I can only see the cheapest and the most expecnsive... I am trying to see the second cheapest or the fifth cheapest etc.
``````|AreaCode|Carrier|Price|
|562     |ATT    |0.05 |
|562     |VZN    |0.03 |
|562     |SPRNT  |0.15 |
|562     |PBELL  |0.09 |
|562     |MJCK   |0.11 |
``````
0
Question by:McFoxx
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2
• +3

LVL 75

Expert Comment

ID: 35012924
This example will give the 3rd one at the top of the list:

SELECT TOP 3 Table1.FIELD1
FROM Table1
ORDER BY Table1.FIELD1 DESC;

mx
0

LVL 75

Expert Comment

ID: 35012941
sorry ignore that.
0

LVL 75

Expert Comment

ID: 35013135
ok ... if say you use the TOP property like this:

SELECT TOP 3 Table1.FIELD4
FROM Table1
ORDER BY Table1.FIELD4;
this will give you the lowest 3.  You could save this query ... say qryLowThree.

Then this query - based on that query will give you the 3rd lowest value:

SELECT Max(qryLowThree.FIELD4) AS [3rd Lowest]
FROM qryLowThree;

Unfortunately,  you can't dynamically input the TOP number with a prompt.  You would  to use vba code and execute the SQL ... where in you could supply the TOP parameter.

mx
0

Author Comment

ID: 35013169
Doesn't seem to be working for me... I will be more specific in the examples below.

I am trying to select the third cheapest rates for each NPA-NXX combo, so that I may export them into a spreadsheet.

After I have that data I would modify the query to return the fourth cheapest rates per NPA-NXX combo and export that data into a separate spreadsheet, etc.

I am trying to present 5 separate documents that show "these are our least expensive rates per NPA-NXX, these are our second lease expensive rates per NPA-NXX, these are our third cheapest rates per NPA-NXX, these are our fourth cheapest rates per NPA-NXX, etc"

The database is quite large, with about 2.4GB worth of these records loaded.
``````What table "master_rate_records" looks like(and it's field names):
+---+---+-------+------+
|NPA|NXX|Carrier|Rate  |
----+---+-------+------+
|201|007|EPMB   |0.0098|
|201|007|FICC   |0.0275|
|201|007|DRTC   |0.0250|
|201|007|FPSD   |0.0072|
|201|007|VVMI   |0.0086|
|201|032|GKNR   |0.0219|
|201|032|ECOV   |0.0190|
|201|032|EPMB   |0.0125|
|201|032|XEOV   |0.0131|
|318|934|YWGU   |0.0061|
|318|934|GKNR   |0.0057|
|318|934|VVIM   |0.0087|
|318|934|KNLB   |0.0073|
+---+---+-------+------+

Desired query result in Access if searching for "third cheapest per NPA-NXX":
+---+---+-------+------+
|NPA|NXX|Carrier|Rate  |
----+---+-------+------+
|201|007|EPMB   |0.0098|
|201|032|ECOV   |0.0190|
|318|934|KNLB   |0.0073|
+---+---+-------+------+
``````
0

LVL 44

Expert Comment

ID: 35013912
SELECT a.AreaCode, a.Carrier, a.Price (SELECT Count(b.Price) FROM myTable b WHERE b.AreaCode=a.AreaCode AND b.Carrier = a.Carrier and b.Price <=A.Price) as PriceCountAsc FROM myTable a Order By a.AreaCode, a.Carrier, a.Price;

When you get a feel for how this query works then you can add conditions which limit which area code and which price count.
0

LVL 9

Expert Comment

ID: 35014339
Try:
SELECT *,  ( SELECT COUNT(*) + 1  FROM table1 AS T2   WHERE T1.NPA=T2.NPA AND T1.NXX=T2.NXX and T1.RATE  > T2.RATE ) AS rank
FROM TABLE1 AS T1
WHERE (SELECT COUNT(*) + 1  FROM table1 AS T2   WHERE T1.NPA=T2.NPA AND T1.NXX=T2.NXX and T1.RATE  > T2.RATE) = 3
ORDER BY NPA, NXX, RATE
0

LVL 9

Expert Comment

ID: 35014349
Or
SELECT *,  ( SELECT COUNT(*)   FROM table1 AS T2   WHERE T2.NPA=T1.NPA AND T2.NXX=T1.NXX and T2.RATE  <= T1.RATE ) AS rank
FROM TABLE1 AS T1
WHERE (SELECT COUNT(*)   FROM table1 AS T2   WHERE T2.NPA=T1.NPA AND T2.NXX=T1.NXX and T2.RATE  <= T1.RATE) <= 3
ORDER BY NPA, NXX, RATE
0

LVL 71

Expert Comment

ID: 36003291
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

LVL 44

Accepted Solution

GRayL earned 1000 total points
ID: 36003292
With the table  PhoneRates as shown in the snippet and running this query:

SELECT a.NPA, a.NXX, a.Carrier, a.Rate, (SELECT Count(b.Rate) FROM PhoneRates b WHERE b.NPA = a.NPA and b.Rate <= a.Rate) AS Ranking
FROM PhoneRates  a WHERE (SELECT Count(b.Rate) FROM PhoneRates b WHERE b.NPA = a.NPA and b.Rate <= a.Rate) = 3 ORDER BY 1;

which I developed from my post at http:#a35013912  it returned this result when 3 was entered after the prompt:

NPA      NXX      Carrier      Rate      Ranking
201      007      EPMB      0.0098      3
318      934      KNLB      0.0073      3

I think this is the solution that was asked for.

``````NPA	NXX	Carrier	Rate
201	007	EPMB	0.0098
201	007	FICC	0.0275
201	007	DRTC	0.025
201	007	FPSD	0.0072
201	007	VVMI	0.0086
201	032	GKNR	0.0219
201	032	ECOV	0.019
201	032	EPMB	0.0125
201	032	XEOV	0.0131
318	934	YWGU	0.0061
318	934	GKNR	0.0057
318	934	VVIM	0.0087
318	934	KNLB	0.0073
``````
0

Expert Comment

ID: 36038945
All,

Following an 'Objection' by GRayL (at http://www.experts-exchange.com/Q_27108230.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.

At this point I am going to re-start the auto-close procedure.

Thank you,

SouthMod
Community Support Moderator
0

LVL 44

Expert Comment

ID: 36007773
Thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corruptâ€¦
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: â€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
###### Suggested Courses
Course of the Month7 days, 21 hours left to enroll