converting record info into 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

 

[MGFoster]

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?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a pivot table (aka cross-tab query). Put the following in the SQL
view of a query definition (design view). This query is untested.

TRANSFORM Sum(Result) As TheValue
SELECT Sample, Lab
FROM TableName
WHERE < your criteria >
GROUP BY Sample, Lab
PIVOT Analyte

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHMBooechKqOuFEgEQIWdACfcCMSedC4pX7rKexWit0I29jRNkAAoIlf
2jYMR5w3yiBoJIjjtKStjG3Z
=evk+
-----END PGP SIGNATURE-----

 

[Dale Peart]

Thanks, that worked fine. However, there is a wrinkle that I didn't know
about that makes the reality of it not work. Some labs analyzed for the
same Analyte multiple times so the 'Sum' adds them up which is not what I
need. I can't figure out how to get the crosstab query to give me all the
numbers and not just the first or last or some other statistic.

Secondly there are two more columns that need to go with every analyte: for
example: the spreadsheet really looks like:
Sample Lab Analyte Result Dilution Method
S1 L1 Al 120 1 M1
S1 L1 Al 118 1 M2
S1 L2 Al 122 1 M1
S1 L3 Al 119 2 M3
S1 L1 Cu 3.5 1 M1
S1 L1 Cu 3.3 1 M2
S1 L2 Cu 3.6 1 M1
S1 L3 Cu 3.8 1 M2
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 AlDilution AlMethod Cu CuDilution
CuMethod
S1 L1 120 1 M1 3.5 1
M1
S1 L1 118 1 M2 3.3 1
M2
S1 L2 122 1 M1 3.6 1
M1
S1 L3 119 2 M3 3.8 1
M2
S2 L1 135 4.3
S2 L2 140 4.5
S2 L3 138
S3 L1 89
S3 L3 92