sorting

[subs]

Ost Ocity Dstate Dcity price
A B C D 34
A B E F 45
X C L Y 78
X C M S 87
T O A B 98
Q W A B 86


Ost and Ocity go together. for example A B is the origin in first
two rows and A B is the destn in last two rows- I want to sort this
data in a report or a query such that the origin and dest are next
to one another i.e in this example the first two rows( A B as origin)
are next to last two rows( A B as destn) . The last two rows should be
3rd and 4 th row and also i want to add a summary row (like
totals)after the first four rows(after every such sequence) . There
are 100 s of such rows in my table. How to sort it and summarize it

Please help thanks

 

[Jerry Whittle]

Select DISTINCT Subs.Ost,
Subs.Ocity,
Subs.Dstate,
Subs.Dcity,
Subs.price
FROM
(SELECT [Ost] & [Ocity] AS Sort12,
Subs.Ost,
Subs.Ocity,
Subs.Dstate,
Subs.Dcity,
Subs.price
FROM Subs
UNION
SELECT [Dstate] & [Dcity] AS Sort12,
Subs.Ost,
Subs.Ocity,
Subs.Dstate,
Subs.Dcity,
Subs.price
FROM Subs
ORDER BY 1) ;