Merging records

[Core]

Hello,

I wonder if there is anyway to do the following.

The company where I work has a database of client details. The table for storing these include fields which relate to the personal details of the client (name, dob, address, contact info, funding stream claimed etc)

The table also includes courses which these clients are enrolled on. Course name, date enrolled, etc etc

No the thing is, these are stored on the same table on the same record.

So, a client that has done 25 courses, would have 25 records, including personal details and course details. Of course the client details are just duplicated across each record, the only fields that change are those relating to the course.

What I want to do is split the table in two, one table for course details and one for personal details. They would join using a one to many relationship. One record for personal info linked to each record on the course table for that learner.

The reason I want to do this is, one, its just better and two, I am going to be merging several databases into one.

Now the problem is the current data cannot be discarded, So I cannot just scrap the old database in place for a new one, the data needs to be retained.

Also there are thousands of records, so it needs to be automated or it will cost a fortune.

Does anyone know it my goal is achievable? And how I would do this?

I know how to do the relationships etc etc the part I cannot solve is the actually splitting of the database.

I was thinking of something like, giving each record with the same name and DOB (or something that would make each duplicated record for a learner uniqe) a number, but again this would need to be automated.

The spliting into personal and course the database, deleting the duplicate records on the personal table and linking those left with the records on the course table using the number assigned previously.

But alais, I cannot automate this.

Okay, thank you for reading and any help would be greatly appriacated.

Many thanks,

 

[Scooterbug]

Off the top of my head, this should be easy, assuming that you are able to create and edit tables.

First, you need to pull the Client information from the table and move it to the new table. Make a query with just the client information on it. To make it so that you only have one client per line, you can add DISTINCT after the select portion of the Sql, or do a count of one of the fields. Use an append query to put the data into the new table, making sure you have a Primary Key field.

Add a field to the orgininal table to store the primary key of the new client table. You can then use a query and link the two tables by a couple of fields (like name and Date of Birth as you said). Then you can use an update query to update the new field with the primary key of the client table.

 

[Core]

Off the top of my head, this should be easy, assuming that you are able to create and edit tables.

First, you need to pull the Client information from the table and move it to the new table. Make a query with just the client information on it. To make it so that you only have one client per line, you can add DISTINCT after the select portion of the Sql, or do a count of one of the fields. Use an append query to put the data into the new table, making sure you have a Primary Key field.

Add a field to the orgininal table to store the primary key of the new client table. You can then use a query and link the two tables by a couple of fields (like name and Date of Birth as you said). Then you can use an update query to update the new field with the primary key of the client table.

Many thanks, I am trying this at the moment, I'll update you on the results

 

[Core]

Worked a charm, thank you very very much!!