Normalize existing table containing data and copying PK value to FK in new table

[coasterman]

I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.

After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.

I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"

Clearly I am doing something wrong so any advice would be appreciated.

P.S I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.

 

[jdraw]

Readers need more info if they are going to give focused help.
What is the subject matter of the business/database in plain English?
Can you provide 4 -5 lines describing a day in the business to help put things into context?
Please show the structures of the existing tables.

 

[plog]

For examples sake let's say you have this poorly structured table:

MainTable
Customer, CustomerAddr, InvoiceNum, InvoiceDate, Product, Quantity
John Smith, 123 Main, 312, 3/1/2013, Tires, 4
John Smith, 123 Main, 312, 3/1/2013, Brakes, 1
Steve Jones, 456 Main, 117, 4/1/2013, Tires, 2
John Smith, 123 Main, 210, 6/1/2014, Filter, 1

So you are going to need 2 new tables--Customers and Invoices. The process essentially the same for both, so let's work with Customers:

1. Create an aggregate query to use as a make table:

SELECT Customer, CustomerAddr FROM MainTable GROUP BY Customer, CustomerAddr

2. After making the table, add an autonumber primary key to it [CustomerID].

3. Add field to MainTable to hold the [CustomerID] value in Customers.

4. Create a query linking Customers to MainTable via Customer and CustomerAddr fields. Turn this into an APPEND query, where you append [CustomerID] from Customers to [CustomerID] in MainTable.

5. Verify all rows in MainTable have a [CustomerID] value. Most likely any blanks are caused by the [Customer] or [CustomerAddr] fields being NULL. A query can't join tables on NULL values.

6. Identify duplicates. There's no such thing as clean data. I guarantee you John Smith was spelled 'Jon Smith' in one spot. Or maybe he moved, or in one record his address is '123 Main Street' which doesn't match '123 Main'. You can fix this manually by toggling between Customers and MainTable, everytime you delete a duplicate in Customers, you change the [CustomerId] in MainTable of the one you deleted to the one you didn't delete.

If there's a lot of duplicates create this table:

tmpDupes
oldID, newID

Then whenever you delete an ID from Customers you put the [CustomerID] of the one you deleted into [oldID] and the value it should become in [newID]. Then when done cleaning Customers, you run an append query, linking [MainTable].[CustomerID] to [tmpDupes].[oldID] and updating [MainTable].[CustomerID] to [tmpDupes].[newID]

 

[coasterman]

Hi,

I should have phrased the question differently on reflection. I am happy with how to normalize the tables so the issue really isn't about the final structure.

What I am struggling with is how to make the initial table relationship, i.e provide the newly created table with the relevant fields culled from the existing table by a make table query and populate the new FK in the Main table with the new Autonumber vale in the new table(s)

I have attached a graphic which better explains. At the moment the critical data is all contained in TBL_MAIN the second existing table can be disregarded at it only refers to historical diary prompts which are not needed albeit I will create a new table for that purpose once the main data is split.

I was hoping this could all be done in the query builder as my SQL is not really up to the task.

many thanks

 

[plog]

Probably still too generic to get your point across. It still seems my method that I detailed will work for this.

Perhaps explain it with actual sample data, not just table structures. My advice would to be to use actual table and field names as well.

 

[coasterman]

Sorry plog I hadn't seen your post, I didn't refresh page. reading now...

 

[sankari117]

I like your post very much....

Regards
SANKARI