Transpose data in a single column over many rows to many columns over single row

[luciegiles]

Hi,

I wish to transpose data in an Access table currently in a single column over many rows to many columns over single row:

Current format:

CHI DateIssued
123456 01/01/10
123456 01/02/10
987654 05/01/10
987654 09/01/10

Desired format:

CHI DateIssued1 DateIssued2
123456 01/01/10 01/02/10
987654 05/01/10 09/01/10

My aim is to calculate the interval between DateIssued1 and DateIssued2.

I've tried this with a crosstab query but unsuccessfully. There could be up to 500 DateIssued fields.

Any help appreciated.

thanks
Lucie

[NeoPa]

Have you looked at CrossTab queries Lucy. There is a wizard available to help you get started.

[luciegiles]

Hi Neo,

Yep, I tried a crosstab before I posted - getting an error message 'too many crosstab column headers' which I assume is to do with the number of DateIssued fields some individuals will have.

L

[NeoPa]

That's the only inbuilt facility I'm aware of I'm afraid.

[luciegiles]

OK, no problem, thanks anyway