Transpose Data in an Access Query

[iluvdeledio]

Hi all,

I've got an ODBC linked table (linked to our office system) in Access 2000 that displays data as follows:

Member# Field Field Value
100001 1 20
100001 2 60
100001 3 0
100001 4 20
100001 5 01/04/2007
100005 1 100
100005 2 0
100005 3 0
100005 4 0
100005 5 01/02/2007
100009 1 0
100009 2 0
100009 3 50
100009 4 50
100009 5 01/01/2007

Does anyone know of a way, query or otherwise, that i can display the data like this:

Member# Field1 Field2 Field3 Field4 Field 5
100001 20 60 0 20 01/04/2007
100005 100 0 0 0 01/02/2007
100009 0 0 50 50 01/01/2007

I've tried the solution at http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Generic%20Function%2 0To%20Concatenate%20Child%20Records' but it doesn't seem to be right.

If this solution should work, can someone please advise so i know to keep working at it. Any other advice would be greatly appreciated

Thanks

Adrian

[MMcCarthy]

Try something like this ...

TRANSFORM First ([Field Value])
SELECT [Member#]
FROM TableName
PIVOT Field;
 

[iluvdeledio]

Thats great, thanks heaps for your help.

I also found out what a crosstab query does, which is good!!!

Thanks again

[MMcCarthy]

Thats great, thanks heaps for your help.

I also found out what a crosstab query does, which is good!!!

Thanks again

You're welcome.