Solved

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

Posted on 2011-03-01
13
430 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
[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
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 70

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

636 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