Result structure dependant on query

[TECHRIKA]

I got a following problem:
I have a table TRAIN_DATA with columns FROM,TO,TO_RAIL,TRAIN_NR. All of the columns are from the text-type. I have some records in that table like:

FROM | TO | TO_RAIL | TRAIN_NR
-----+------+---------+---------
VAL1 | VAL2 | 1 | 1111
VAL2 | VAL3 | 4 | 1121
VAL3 | VAL4 | 6 | 1156
VAL7 | VAL8 | 9 | 1456

Now I would like to create a query, and bind it into a report to display the data in following form:

VAL1 1
1111
VAL2 4
1121
VAL3 6
1156
VAL4
VAL7 9
1456
VAL8

Got the idea?
If the value of TO of the row is equals with the value of FROM in the next row - display it only once.
This example is about a traveling table. If we travel 'through' the city but just change the train in that city, the city should be displayed only once, otherwise if the destination of the current train doesn't match the source of the next train - display both cities...

 

[dhookom]

If I understand correctly, you can create a query with this SQL:
[Blue]

SELECT TRAIN_DATA.FROM, TRAIN_DATA.TO, 
TRAIN_DATA.TO_RAIL, TRAIN_DATA.TRAIN_NR, 
IIf(IsNull([TRAIN_DATA_1].[TO]),[TRAIN_DATA].[TO],Null) AS Expr1
FROM TRAIN_DATA LEFT JOIN TRAIN_DATA AS TRAIN_DATA_1 ON TRAIN_DATA.TO = TRAIN_DATA_1.FROM;

[/Blue]
Display this in a report in the required format.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.