query help (1 Viewer)

[syodb]

Let's consider a table as follows.
In:

A B C A1 A2 A3 B1 B2 B3 C1 C2 C3

need an Access db query to get this output?:
Output:

A A1 A2 A3 
B B1 B2 B3
C C1 C2 C3

 

[June7]

UNION query can rearrange data to that output.

SELECT A AS f0, A1 AS f1, A2 AS f2, A3 AS f3 FROM table
UNION SELECT B, B1, B2, B3 FROM table
UNION SELECT C, C1, C2, C3 FROM table;

UNION won't allow duplicate records. Might need UNION ALL.

 

[syodb]

Thank you, the UNION query works well, the only thing is that the result is sorted and that is not desired at all times. How to disable the sorting, or add an increment ID to the UNION so that first row shows up on the top at all times?.

 

[The_Doc_Man]

Sorted? Are we talking about all of the "A" records first, then "B" records, then "C" records - or are we talking some other sort order?

The problem is that if you read the UNION query, the order I just mentioned SHOULD be expected because you pull all of the records in that order. The first SELECT pulls the "A" records; the second SELECT pulls the "B" records; the third SELECT pulls the "C" records. The effect of the UNION operation is to append the B records after the A records and then append the C records after the B records.

There is no way to disable the sorting if you take a look at the previous paragraph that describes the way Access will do this. Is it your intention that you would want the A, B, and C records for the first original record, followed by the A, B, and C records for the next original record, and so on?

If so, you need to have an "ID" number in the original record and then do a multi-layered SELECT query.

QueryA:

SELECT ID, "A" AS SEGNO, A AS f0, A1 AS f1, A2 AS f2, A3 AS f3 FROM table
UNION SELECT ID, "B" AS SEGNO, B, B1, B2, B3 FROM table
UNION SELECT ID, "C" AS SEGNO, C, C1, C2, C3 FROM table;

Then write a sorting query:

QueryB:

SELECT ID, SEGNO, F0, F1, F2, F3 FROM Query A ORDER BY ID, SEGNO ;

Use QueryB to look at the records.