access records 1

[texaspanama]

I have two tables (SQL database),

Table 1:
Carrier Flight
AA 0001
Table 2:
Carrier Flight Class
AA 0001 C
AA 0001 F
AA 0001 Q
AA 0001 R
AA 0001 W

The report should look like this:

Carrrier Flight Class1 Class2 Class3 Class4 Class5
AA 0001 C F Q R W

Any suggestions how to?

Thank you

 

[IanWaterman]

You will have to do a manual cross tab and group by Carrier.

In details create five formulae

eg
@class1
If {Class} = "C" then {Class} else ""

@class2
If {Class} = "F" then {Class} else ""

etc
Suppress details and in group footer add summaries to display max of each formula.

Hope this answers your question.

Ian

 

[lbass]

I'd try an inserted crosstab first to see if that met your requirements. Use {table.class} as the column and either use two row fields, {table.carrier} and {table.flight}, or concatenate these two fields in a formula and use the formula as the row. For the summary, use a maximum of {table.class}.

-LB