Combine Fields

[naiveprogrammer]

I am trying to normalize a db that I have imported from an ODBC source. I
have 2 sets of 6 different fields that I want to combine into 2 fields.
Example of the current layout-

Employee ID Date 1 Rate 1 Date 2 Rate 2 Date 3 Rate 3 Date 4 Rate 4 Date 5
Rate 5 Date 6 Rate 6

This is what I want to acheive-

Employee ID Date Rate

I want all the dates in one field and all the rates in one field. What is
the QUIKEST way to get this info the way I need it?

Thanks!!!

 

[Ken Snell [MVP]]

Use a UNION query to normalize the data, then use that union query as the
table source for an append query to put the normalized data into the
permanent table.

qryUnionData:

SELECT T1.[Employee ID], T1.[Date 1], T1.[Rate 1]
FROM TableName AS T1
UNION ALL
SELECT T2.[Employee ID], T2.[Date 2], T2.[Rate 2]
FROM TableName AS T2
UNION ALL
(etc.)
SELECT T5.[Employee ID], T5.[Date 5], T5.[Rate 5]
FROM TableName AS T5;


qryAppendData:

INSERT INTO NewTableName
([Employee ID], [Date], [Rate])
SELECT * FROM qryUnionData;

Also, do not use Date as the name of a table's or query's field. It and many
other words are reserved words in ACCESS, and can create serious confusion
for ACCESS and Jet. See these Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763