query to parse data

[Dan]

I have normalized my original data that was in "sheet" format from excel. I
have 72 fields that contain abundance values for plant species and each
record has the location and date details (from where the plant was seen and
on what day). This data is currently still in "sheet" stlye. What
query/queries do I need to run to get the data into a better structure, ie
each positive plant abundance for each species in a separate row along with
location and date?
Thanks

 

[Jeff Boyce]

Dan

If you are saying you have something like:

PlantType1 DateDetail1 Location1 PlantType2 DateDetail2 Location2 ...
PlantTypeN...
or even:
PlantType1Measurement1 DateDetailMeasurement1 LocationMeasurement1
PlantType1Measurement2...

you can use a query (repeatedly) or a series of queries that grabs a single
plant's info for a single DateDetail/Location entry and appends it to your
permanent table. Rinse & repeat...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Dan]

Thanks Jeff,
is there a quick method of repeating these kind of queries without creating
a new query for each field? as I have 72 fields this could become rather
laborious, ie retyping in each query and altering the expression text to
match each fields details.

Dan

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NormalizeDenormalize.mdb" which illustrates a VBA solution
for this. Naturally, you'll have to modify it to meet your table's
structure.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jeff Boyce]

Dan

Roger offers a VBA approach ... to use it, you'd need to build/modify some
code.

If this is a one-time only conversion, it might take less time to use the
queries.

When I'm faced with this kind of conversion, I create one query, get it
working, then use it as a template to cycle through all the fields. Unless
this will be a regular need with changing fieldnames, in which case I'd
probably build something like what Roger's offered!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/