sort a table uniquely

  • Thread starter Thread starter subs
  • Start date Start date
S

subs

i have got a table with the following data

A B 3400 USD
E F 1200 USD
O M 500 USD
B A 211 USD
C D 333 USD
F E 332 UD


i need the above table in access to be sorted this way


ocity dcity price
A B 3400 USD
B A 211 USD
E F 1200 USD
F E 332 USD
O M 500 USD
C D 333 USD

for example if there is ocity and destn city as A and B respecitvely
in one row, and then they are reversed in another row i.e ocity and
dcity as B and A, i need those two rows to appear immediately one
below another.

Same with E and F in above example. I need a query which can sort a
table as in the above example.pls help

thanks
 
i have got a table with the following data

A B 3400 USD
E F 1200 USD
O M 500 USD
B A 211 USD
C D 333 USD
F E 332 UD


i need the above table in access to be sorted this way


ocity dcity price
A B 3400 USD
B A 211 USD
E F 1200 USD
F E 332 USD
O M 500 USD
C D 333 USD

for example if there is ocity and destn city as A and B respecitvely in
one row, and then they are reversed in another row i.e ocity and dcity
as B and A, i need those two rows to appear immediately one below
another.

Same with E and F in above example. I need a query which can sort a
table as in the above example.pls help

thanks
Click to expand...

A UNION query that puts all the cities in a single column with another
computed column that indicates whether it is a ocity versus a dcity might
be able to give you this sort order. Otherwise no obvious method comes
to mind.
 
A UNION query that puts all the cities in a single column with another
computed column that indicates whether it is a ocity versus a dcity might
be able to give you this sort order.  Otherwise no obvious method comes
to mind.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com- Hide quoted text -

- Show quoted text -
Click to expand...

Can you pls explain step by step how to do that? i am quite new to
this. Thanks
 
subs said:
i have got a table with the following data

A B 3400 USD
E F 1200 USD
O M 500 USD
B A 211 USD
C D 333 USD
F E 332 UD


i need the above table in access to be sorted this way


ocity dcity price
A B 3400 USD
B A 211 USD
E F 1200 USD
F E 332 USD
O M 500 USD
C D 333 USD

for example if there is ocity and destn city as A and B respecitvely
in one row, and then they are reversed in another row i.e ocity and
dcity as B and A, i need those two rows to appear immediately one
below another.
Click to expand...


This borders on the inpossible. I can not see any logical
reason to put A before E or O before C.

First, tables om a relational database are not and can not
be sorted. The only way to present a sorted view of the
records is to use a query with an Order By clause. (In
reports, this is specified by using Sorting and Grouping)

Maybe you can get semi close to at least pairing A,B and E,F
by sorting on an expression like ocity**2 + dcity**2, which
requires that the two fields are either number fields or
they can be converted to numbers in a consistent way.
 
subs said:
i have got a table with the following data

A B 3400 USD
E F 1200 USD
O M 500 USD
B A 211 USD
C D 333 USD
F E 332 UD


i need the above table in access to be sorted this way


ocity dcity price
A B 3400 USD
B A 211 USD
E F 1200 USD
F E 332 USD
O M 500 USD
C D 333 USD

for example if there is ocity and destn city as A and B respecitvely
in one row, and then they are reversed in another row i.e ocity and
dcity as B and A, i need those two rows to appear immediately one
below another.

Same with E and F in above example. I need a query which can sort a
table as in the above example.pls help

thanks
Click to expand...

This query seems to work:

select distinct ocity,dcity,price,curr from (
select ocity as sort, ocity,dcity,price,curr from cities
union
select dcity as sort,ocity,dcity,price,curr from cities
order by sort) as q

Create a new query and switch it to SQL View. Paste the above statement in
and replace the table name with you actual table name.
 
subs said:
i have got a table with the following data
Click to expand...
A       B         3400 USD
E       F         1200 USD
O       M         500   USD
B        A         211   USD
C        D         333    USD
F         E         332    UD
Click to expand...
i need the above table in access to be sorted this way
Click to expand...
ocity   dcity    price
A        B        3400   USD
B        A        211     USD
E        F        1200    USD
F        E          332     USD
O        M         500   USD
C        D        333   USD
Click to expand...
for example if there is ocity and destn city as A and B respecitvely
in one row, and then they are reversed in another row i.e  ocity and
dcity as B and A, i need those two rows to appear immediately one
below another.
Click to expand...

This borders on the inpossible.  I can not see any logical
reason to put A before E or O before C.

First, tables om a relational database are not and can not
be sorted.  The only way to present a sorted view of the
records is to use a query with an Order By clause.  (In
reports, this is specified by using Sorting and Grouping)

Maybe you can get semi close to at least pairing A,B and E,F
by sorting on an expression like ocity**2 + dcity**2, which
requires that the two fields are either number fields or
they can be converted to numbers in a consistent way.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

no may be i was not clear- all i need is to sort all the ROWS such a
way that A, B row come next to B, A row, Similarly E , F row come
next to F, E row. O, M does not have equivalent M, O . So i donot
worry about it- let that row be anywhere in the table. Hopefully i
was clear this time. any SQL query to group or sort those rows ?
 
subs said:
no may be i was not clear- all i need is to sort all the ROWS such a
way that A, B row come next to B, A row, Similarly E , F row come
next to F, E row. O, M does not have equivalent M, O . So i donot
worry about it- let that row be anywhere in the table. Hopefully i
was clear this time. any SQL query to group or sort those rows ?
Click to expand...


If the city fields are numeric, the query I was suggesting
would be like:

SELECT ocity, dcity, price
FROM the table
ORDER BY ocity**2 + dcity**2

OTOH, Bob and Rick's idea should also work if each ocity can
be associated with only one dcity. If each ocity can be
associated with multiple dcitys, then I thing a sort2 field
would be needed.

Here's a different idea that might be useful:

SELECT ocity, dcity, price
FROM the table
ORDER BY IIf(ocity > dcity, dcity, ocity), IIf(ocity >
dcity, ocity, dcity)
 
no may be i was not clear- all i need is  to sort all the  ROWS sucha
way that  A, B  row come next to B, A row, Similarly E , F row come
next to F, E  row.  O, M does not have equivalent M, O . So i donot
worry about it- let that row be anywhere in the table.  Hopefully i
was clear this time. any SQL query to group or sort those rows ?
Click to expand...

If the city fields are numeric, the query I was suggesting
would be like:

SELECT ocity, dcity, price
FROM the table
ORDER BY ocity**2 + dcity**2

OTOH, Bob and Rick's idea should also work if each ocity can
be associated with only one dcity.  If each ocity can be
associated with multiple dcitys, then I thing a sort2 field
would be needed.

Here's a different idea that might be useful:

SELECT ocity, dcity, price
FROM the table
ORDER BY IIf(ocity > dcity, dcity, ocity), IIf(ocity >
dcity, ocity, dcity)

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Marsh

You are right- each ocity can be associated with different dcities and
every dcity can be associated with different ocities.
 
subs said:
You are right- each ocity can be associated with different dcities and
every dcity can be associated with different ocities.
Click to expand...


Where do things stand at this point? The more I think about
it, the more it seems like my last idea can do what you
want.
 
I used Subs as table name and [X] as 4th field name. Use these queries ---
Query- Subs_1 --
SELECT subs_1.ocity, subs_1.dcity, subs_1.price, subs_1.x, "A" AS z
FROM subs LEFT JOIN subs AS subs_1 ON (subs.dcity = subs_1.ocity) AND
(subs.ocity = subs_1.dcity)
WHERE (((subs_1.ocity) Is Not Null))
ORDER BY "A", subs_1.ocity;

Query- Subs_2 --
SELECT Subs_1.ocity, Subs_1.dcity, Subs_1.price, Subs_1.x, Subs_1.z
FROM Subs_1
UNION SELECT Subs.ocity, Subs.dcity, Subs.price, Subs.x, "B" AS z
FROM Subs LEFT JOIN Subs_1 ON (Subs.ocity = Subs_1.ocity) AND (Subs.dcity =
Subs_1.dcity)
WHERE (((Subs_1.ocity) Is Null) AND ((Subs_1.dcity) Is Null));

Query- Subs_3 --
SELECT Subs_2.ocity, Subs_2.dcity, Subs_2.price, Subs_2.x
FROM Subs_2
ORDER BY Subs_2.z, Subs_2.ocity;
 
Back
Top