combine multiple fields into a single field with multiple rows

J

JMalecha

Okay I have made my database with the fields of:
Monitor_Well, Date, Specific_Conductance, pH, Antimony, Arsenic, Barium, etc.

There are several Monitor Well Numbers and several sample dates. The
constituents results are reported with the constituent name as the Field Name
in Column format.

The programs we need to export to require the fields of:
Monitor_Well, Date, Constituent_Name, Result

My question is does Access have a way to convert all of my column fields
into rows of information? The only thing I have been able to come up with is
to analyze my query with Microsoft Excel and copy and paste transpose
everything. Is there a better solution? Thanks!
 
K

KARL DEWEY

Yep! It is known as a union query.
SELECT Monitor_Well, Date, "Antimony" AS Constituent_Name, Antimony AS Result
FROM YourTable
UNION ALL SELECT Monitor_Well, Date, "Arsenic" AS Constituent_Name, Arsenic
AS Result
FROM YourTable
UNION ALL SELECT Monitor_Well, Date, "Barium" AS Constituent_Name, Barium
AS Result
FROM YourTable
etc. ;

NOTE - A union query cannot be viewed in design view, only in SQL view.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top