Tranportation query - interesting one

[subs]

ocity ost dcity dstate carrier price rank
a b c d ram 1000 1
a b c d ram1 1200 2
a b c d ram2 1233 3

p q r s ram1 1200 1
p q r s ram 1300 2
p q r s ram2 1340 3

o w m t ram1 1300 1
o w m t ram 1500 2



Pls see the above data. the carriers are ranked based on price for
every set of ocity/ost/dcity/dstate. This pattern of data continues
for rest of the table. now i want to have a query which will extract
all lanes where ram is ranked 1 and the associated lanes . the
associated lanes is the key here. For example if i run that query , it
will extract the first three rows in the above table- not just the
first row. Similarly if i run the query for ram1 as rank 1, i want it
to extract the last six rows of the table.

Can anyone pls help me with a query here- either SQL or in design
view. Thanks

 

[Marshall Barton]

ocity ost dcity dstate carrier price rank
a b c d ram 1000 1
a b c d ram1 1200 2
a b c d ram2 1233 3

p q r s ram1 1200 1
p q r s ram 1300 2
p q r s ram2 1340 3

o w m t ram1 1300 1
o w m t ram 1500 2

Pls see the above data. the carriers are ranked based on price for
every set of ocity/ost/dcity/dstate. This pattern of data continues
for rest of the table. now i want to have a query which will extract
all lanes where ram is ranked 1 and the associated lanes . the
associated lanes is the key here. For example if i run that query , it
will extract the first three rows in the above table- not just the
first row. Similarly if i run the query for ram1 as rank 1, i want it
to extract the last six rows of the table.

Query design view is too clumsy for this kind of thing.
Here's an SQL statement that I think does what you want:

SELECT T.*
FROM table As T
INNER JOIN (SELECT ocity, dcity
FROM table As X
WHERE carrier = [Enter Carrier]
And Rank = 1) As R
ON T.ocity = R.ocity And T.dcity = R.dcity

You probably(?) don't want to use a parameter prompt but you
did not explain how the carrier is specified.