May 17 2005, 10:15 AM
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?
May 17 2005, 10:26 AM
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
May 17 2005, 10:41 AM
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!
May 17 2005, 11:54 AM
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:
May 17 2005, 12:25 PM
THANK YOU! I can't believe I missed that...
May 17 2005, 12:50 PM
You're welcome. Sometimes fresh eyes can see better.
May 17 2005, 12:53 PM
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.
May 17 2005, 12:59 PM
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, *
May 17 2005, 02:11 PM
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!!!
May 18 2005, 09:21 AM
May 19 2005, 12:02 PM
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here