Transposing field data 1

[TrekBiker]

I have an input table with fields in this simplified form.

Company, Category, Score, Comment

The data looks like this

Company A, Category A, Score A, Comment A
Company A, Category A, Score B, Comment B
Company A, Category A, Score C, Comment C
Company A, Category A, Score D, Comment D

then repeated for 40 companies and 10 categories.

For reporting purposes I'd like the data in the form

Company A, Category A, Score A, Score B, Score C, Comment A, Comment B, Comment C, Comment D

Is there a way of doing this without using a huge set of queries that are then joined?

 

[dhookom]

Are there only A - D? You can create a multi-value crosstab query using the solution at faq701-4524.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Duane

Thanks, I'll have a look at this. Some combinations of Company and Category can have different numbers of scores, from 3 to 6.

Could your suggestion be applied for the entire table, which has 40 companies, 10 categories and the variable number of scores?

Much appreciate your help, as ever.

 

[dhookom]

It should work or at least be adaptable. I don't have the time to create a sample table and test.

Duane
Hook'D on Access
MS Access MVP

 

[TrekBiker]

Thanks very much Duane.

I've tried the Northwind example and it looks to do the job, goodness knows how.