If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
combine multiple fields into a single field with multiple rows
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! |
Thread Tools | |
Display Modes | |
|
|