Convert Data from Unnormalized table

[bymarce]

When I first started using Access and had no understanding of normalization I
made a database for scientific data whose data table contained fields for
SampleID, DataID, and one each experiment we conducted and we put data in it.
This table contained over 100 fields. I later realized my error and made a
new database whose data table contains the following fields: SampleID,
DataID, Property, Method, Numeric Results, TextResults, and Units. Is there
a way I can get the from the old table into the format of the new table with
out manually retyping all of it? I'd like the field names to go into the
Property field and any value in the table to go into the results field. One
record from the old table would have between 1 and 10 records in the new
table. It would be alot less work to fix the records from that point and
produce fewer typing errors. Thanks.
Marcie

 

[Paul Shapiro]

Look into Append queries. They let you use a Select statement to copy data
from one table to another.

Before you go too far you might want to do some reading about normalization,
to make sure you're finished with structural changes. Attributes like Units,
Property and maybe Method sound like they should probably be foreign keys
from parent tables rather than free text.

 

[John W. Vinson]

When I first started using Access and had no understanding of normalization I
made a database for scientific data whose data table contained fields for
SampleID, DataID, and one each experiment we conducted and we put data in it.
This table contained over 100 fields. I later realized my error and made a
new database whose data table contains the following fields: SampleID,
DataID, Property, Method, Numeric Results, TextResults, and Units. Is there
a way I can get the from the old table into the format of the new table with
out manually retyping all of it? I'd like the field names to go into the
Property field and any value in the table to go into the results field. One
record from the old table would have between 1 and 10 records in the new
table. It would be alot less work to fix the records from that point and
produce fewer typing errors. Thanks.
Marcie

A "Normalizing Union Query" - or, with 100 fields, probably a couple or three
of them - is just the ticket here. You can use the SQL window to construct a
query like:

SELECT SampleID, DataID, "ThisField" AS Property, [ThisField] AS TextResults
FROM yourtable
WHERE [ThisField] IS NOT NULL
UNION ALL
SELECT SampleID, DataID, "ThatField" AS Property, [ThatField] AS TextResults
FROM yourtable
WHERE [ThatField] IS NOT NULL
UNION ALL
<etc through all the fields>

If you get the QUERY TOO COMPLEX error when you try to open this query, split
it into two queries.

Base an Append query on this UNION query to append the data into your
normalized table.

I don't know how you currently have the data stored, so I'm not sure how to
get data into the Method, NumericResults and Units field - but you can store
anything under 255 bytes in TextResults and then run update queries to parse
it out into the other fields. Post back with an example if you need help.

 

[Peter Hibbs]

Marcie,

Have a look at the Excel to Access Converter Utility which does just
that automatically (ignore the Excel bit in the name, just export your
data as a .csv file and import that file into the converter program)
at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

HTH

Peter Hibbs.