Normalizing non-normal tables already containing data;OR changing columns into rows
Hi Paul,
Quote:
> Unfortunately,
> the table contains one record for each item and has approx. 180 columns
> of measurement values. I would like to normalize this table...
Admirable goal! <g>
Quote:
> However, there is no easy way of
> doing this.
One possibly too-tedious way would be to create an "append" query which
appends measurement1 to Measurement; then modify to append MeasureMent2 to
Measurement etc. Then repeat this 180 times. Not fun, but sometimes brute
force is the best way.
Quote:
> My other alternative is to write Access Basic
> DAO code (I'm in version 2.0), but I'm not quite sure how to write the
> procedure.
This wouldn't be too hard to do either. Theres a bruteforce method, and an
"elegant" method that may be more trouble than it's worth. Brute force:
dim db as database
dim rsBad as recordset
dim rsMeasurements as recordset
set db = currentdb()
set rsBad = db.openrecordset("tblBadlyNormalizedData")
set rsMeasurements = db.openrecordset("tblMeasurements")
rsBad.movefirst
do until rsBad.eof
rsMeasurements.addnew
rsMeasurements!id = rsBad!ID
rsMeasurements!Measurement = rsBad!Measurement1
rsMeasurements.update
rsMeasurements.addnew
rsMeasurements!id = rsBad!ID
rsMeasurements!Measurement = rsBad!Measurement2
rsMeasurements.update
rsMeasurements.addnew
rsMeasurements!id = rsBad!ID
rsMeasurements!Measurement = rsBad!Measurement3
rsMeasurements.update
rsBad.movenext
'etc
loop
With cut-and-paste this might not be too bad.
You could also loop through the "fields" collection of the recordset and
add the rows that way. I'll spare you (and me) the example for this unless
you need help with it.
--
-- Jim F, FMS
http://www.*-*-*.com/
