Solved

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

Posted on 2011-03-01
13
424 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 68

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 250 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now