combine multiple fields into a single field with multiple rows

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!

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.

--
Build a little, test a little.


"JMalecha" wrote:

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!