converting record info to fields

[Dale Peart]

This may be the wrong subgroup for this question but perhaps someone can
recommend a better one.
I have a table that looks like:
Sample Lab Analyte Result
S1 L1 Al 120
S1 L2 Al 122
S1 L3 Al 119
S1 L1 Cu 3.5
S1 L2 Cu 3.6
S1 L3 Cu 3.8
S2 L1 Al 135
S2 L2 Al 140
S2 L3 Al 138
S2 L1 Cu 4.3
S2 L2 Cu 4.5
S3 L1 Al 89
S3 L3 Al 92
....
And I would like to transpose this to a table that looks like this:
Sample Lab Al Cu
S1 L1 120 3.5
S1 L2 122 3.6
S1 L3 119 3.8
S2 L1 135 4.3
S2 L2 140 4.5
S2 L3 138
S3 L1 89
S3 L3 92

Can this be done in Access or is there a better way?

Dale Peart

 

[Adrian Jansen]

You are denormalising the data to do this. Best would be a crosstab query,
which will return the data in the form you want, leaving the table as it is.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Andrew Smith]

What you're describing is a crosstab query. There is a crosstab query wizard
that should sort it out for you.