sort a table uniquely

[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

 

[Rick Brandt]

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

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.

 

[subs]

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 -

Can you pls explain step by step how to do that? i am quite new to
this. Thanks

 

[Marshall Barton]

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.

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.

 

[Bob Barrows]

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

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]

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.

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 -

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 ?

 

[Marshall Barton]

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 ?

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)

 

[subs]

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 ?

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 -

Marsh

You are right- each ocity can be associated with different dcities and
every dcity can be associated with different ocities.

 

[Marshall Barton]

You are right- each ocity can be associated with different dcities and
every dcity can be associated with different ocities.

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.

 

[karl dewey]

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;