Transform Data in a table

[Chuck W]

I receive an extract from someone that comes as text file. It has fields
such as PatientNumber, AccountNumber, AdmitDate, DischargeDate, OrderSet,
Source, HF1_Numerator, HF1_Denominator......HF4_Numerator and
HF4_Denominator. What I want to do is to transform the data so that there is
one column for Numerator and one for Denominator. I also would need a new
field called Measure that would have the following four values: HF1, HF2, HF3
and HF4. I want to keep all of the other fields as is. Right now I copy the
data into Excel and do about 15 minutes of cutting and pasting. I will need
to run this often and was wondering if there was a way to transform the data
Access.

Thanks,

 

[Tom Wickerath]

Hi Chuck,

You can use a Union query to help accomplish this goal. A union query is a
read-only query, and does not have an associated design view, but you can use
this as a source of data for an append query to append (add) the records to a
new table. Create a new query, and then switch to SQL View. Enter something
like this:

SELECT PatientNumber, AccountNumber, AdmitDate, DischargeDate,
OrderSet, Source, "HF1" AS Measure, HF1_Numerator AS Numerator,
HF1_Denominator AS Denominator
FROM TableName

UNION

SELECT PatientNumber, AccountNumber, AdmitDate, DischargeDate,
OrderSet, Source, "HF2" AS Measure, HF2_Numerator AS Numerator,
HF2_Denominator AS Denominator
FROM TableName

UNION

SELECT PatientNumber, AccountNumber, AdmitDate, DischargeDate,
OrderSet, Source, "HF3" AS Measure, HF3_Numerator AS Numerator,
HF3_Denominator AS Denominator
FROM TableName

UNION

SELECT PatientNumber, AccountNumber, AdmitDate, DischargeDate,
OrderSet, Source, "HF4" AS Measure, HF4_Numerator AS Numerator,
HF4_Denominator AS Denominator
FROM TableName


Save this query. Then use it as a source of data for an append query, to add
these records to a new table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________