extracting data from one table and making another with different design

[Michele Stephenson]

I have tables that were created in Paradox with a bad
database design. I am restructuring in Access. All of
the data goes across in fields . Basically, an air
sample was taken and a lot of data was reported about it.
For example.location, duration, etc. That is okay. They
also reported the chemicals found in the sample all in
the same record (about 50 fields of info). I cannot
search the data by chemical with it structured this way.

How structured now:
Fields
AirSmple benzeneairqual benzconc tolueneairqual tolconc
sampleid1 good 0.8 bad 0.2
sampleid3 good 0.4 good 1.4



I want to split out the data into another table and
structure it like this and I will link to the primary
data table by sample ID..

Fields
airsampID chemical Airquality concentr etc..
1 benzene good 0.8
1 toulene marginal 0.9
2 benzene bad 1.4

My question.Is there an easy way to do this without
retyping or copying and pasting the data. I have over
2,000 records with about 11 different chemicals to split
out.

Thank you..Thank you...Michele

 

[Jeff Boyce]

Michele

Look up previous responses offering a "normalizing UNION query". Try
Google.com with this topic if your newsgroup reader doesn't find any. Also
check the .queries group.

Good luck

Jeff Boyce
<Access MVP>