Display Field Data in Columns

[hose3]

I have a scheduling database that has the following fields and sample data in a table.

SCHEDULEAIRCREWID	SCHEDULEID	AIRCREWID
1	1	1
2	1	2
3	2	7
4	2	10
5	2	15
6	3	3
7	3	6
8	3	15
9	4	2
10	4	7

What I am trying to do, is for every SCHEDULEID, I'd like the AIRCREWID to display in columns either in a query/report. So I'd basically like to see the following:

SCHEDULEID	AIRCREWID	AIRCREWID	AIRCREWID
1	1	2	(NULL)
2	7	10	15
3	3	6	15
4	2	7	(NULL)

Any help would be appreciated.

[CJ_London]

you need another column to indicate the aircrew position (might be 'captain', 'copilot' etc or 1,2,3) and then you can use a crosstab query.

Your crosstab heading would then be

ScheduleID...Captain...Copilot.....

or

ScheduleID..Crew1..Crew2,,,Crew3

you cannot have the headings you want because each column heading has to be different

You can create a numerical position using a subquery, but seem to recall that crosstabs and subqueries don't mix.

Alternative, if you don't have too many aircrew, is to use the aircrewid as a column heading and with a crosstab you would end up with something like

ScheduleID...1..2..3..6..7..10..15
1.................X..X
2................................X...X...X
3.........................X..X...........X
4.....................X.........X