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!!!

Create your table, then use a series of append queries to add the data to the table.
Change which Date and Rate fields are being appended with each query, making sure to exclude records where the date and rate field you are appending are null.

hope this helsp
-AJ

That does help! I don't have any experience with append queries but when I try to run it, I get an error message stating- "Duplicate output destination 'Employee'. Can you tell me what's that about???

INSERT INTO Normalization ( Employee, [Date], Rate )
SELECT ActiveEmpPR.[Active Employees].Employee, ActiveEmpPR.Pay_Rate_Date1_, ActiveEmpPR.Pay_Rate_1, *
FROM Normalization, ActiveEmpPR;

Thanks so much!

Look at your FROM clause. You are trying to append from Normalization TO Normalization. The field list does not include any fields from Normalization so all you need to do is change the FROM clause to:

FROM ActiveEmpPR;

THANK YOU! I can't believe I missed that...

You're welcome. Sometimes fresh eyes can see better.

Also you should NOT use Date as a field name. Date is a reserved word in Access.

By the way, you are really combinng fields here. What you are doing is taking repeating groups and separating them into single records.

Now when I try to run the query I get an error msg saying "The INSERT INTO statement contains the following unknown field name: 'Pay_Rate_Date_2'. Make sure you typed the name corrrectly, and the try the operation again." I only want to pull Pay_Rate_Date1 and Pay_Rate_1 for the time being. Why is it asking me this?

Thanks to all of you for all of your help!



INSERT INTO Normalization ( ID, Change, Rate )
SELECT ActiveEmp_PR.Employee, ActiveEmp_PR.Pay_Rate_Date1_MMDDYYYY, ActiveEmp_PR.Pay_Rate_1, *
FROM ActiveEmp_PR;

The * at the end of the SELECT is causing it to get all the fields including those you have already gotten

Get rid of the * right now if not sooner!!!

Thanks!

You're welcome.