Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-01
13
Medium Priority
?
432 Views
Last Modified: 2012-05-11
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 |

Open in new window

0
Comment
Question by:McFoxx
  • 3
  • 3
  • 2
  • +3
13 Comments
 
LVL 75
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
ID: 35012941
sorry ignore that.
0
 
LVL 75
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:McFoxx
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|
+---+---+-------+------+

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
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

by:chwong67
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

by:chwong67
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

by:Qlemo
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

by:
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

Open in new window

0
 

Expert Comment

by:South Mod
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

by:GRayL
ID: 36007773
Thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question