How do I show convert vertical entries to show horizontally

[Lynn73]

Is it possible to convert fields listed down to show across.

IDNo Code Position
AAA B01 1
AAA C01 2
AAA D01 3
AAA E01 4

I want to show it as
IDNo Code Code Code Code
AAA B01 C01 D01 E01

Is this possible using Access 2003?

 

[Lissa]

Try searching for ways to create "pivot tables". I think that's what you are describing....

 

[Lynn73]

I actually want to show the data rather than count it. Is a pivot the best way to go for that?

 

[kidrobot]

Transpose function? You can do this in Excel, I have never tried in Access.

 

[stopher]

You can create a crosstab query (a kind of pivot table).

If you table name is myTable then the query will be:

TRANSFORM First(Code) AS FirstCode
SELECT IDNo
FROM myTable
GROUP BY IDNo
PIVOT Position

Note that the field "Position" will be used as your header i.e. in your example data you will have column headers 1-4.

hth
Stopher

 

[Lynn73]

This works great!!!
Thanks for your help!

One last thing - I have some entries where the code field may be empty - is it possible to include them in this query or would I be better running a separate append query?
Thanks

 

[stopher]

One last thing - I have some entries where the code field may be empty - is it possible to include them in this query or would I be better running a separate append query?

I'm not quite sure what you mean. Can you show an example of what you want you want to see for a blank value for code.
Stopher

 

[Lynn73]

Some records will be populated with a code and some may not yet be coded or may never have a code allocated to them

IDNo Code Position
AAA B01 1
AAA C01 2
AAA D01 3
AAA E01 4
BBB
CCC

When I run the crosstab query it only listed the records with a code allocated and doesn't include any rows without a code.

Thanks