Importing Old DB into New (1 Viewer)

DFlynn24

Registered User.
Local time
Today, 16:41
Joined
May 1, 2019
Messages
29
Greetings,

At work I have inherited a database from a predecessor that is incredibly inefficient. In essence, it contains case information and subject information where there can be multiple subjects to one case. For example this is how it is entered:

Date Location Subject Name
1/1/2000 123 Main St 1. John Smith 2. Jane Doe

Subject DOB Gender
1. 1/1/1980 2. 1/1/1985 1. Male 2. Female


I already have the tables created in the new database with the proper ID fields and relationships defined. However, I am unsure if there is a way to easily import the old data. For instance, using the above example, I would need John Smith and Jane Doe to be classified under CaseID 1 but SubjectID 1 and 2, respectively.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 16:41
Joined
Jan 23, 2006
Messages
15,364
Can you provide a few more details about the application?
You mention CASE but I don't see a Case column or heading???
Can you post a jpg/png of your tables and relationships?
Since it's a new database (unpopulated) perhaps you could post a copy in zip format.

Here is a very high level model for Case investigations from Database Answers.org

Are you familiar with Normalization? If not, here is a link that may help.
 
Last edited:

DFlynn24

Registered User.
Local time
Today, 16:41
Joined
May 1, 2019
Messages
29
I don't know how much information I can provide on here because it is law enforcement sensitive. And you're right you don't see a case column in my example because it does not exist in the old database.

I don't know if this will explain it better: right now everything in my example is in one table. in my new database this would be two tables. One for the incident information (CaseID (autonumber), date, incident location, etc.) and one for subject information (SubjectID (autonumber), CaseID, Subject Name, Subject Gender).

Basically, I am wondering if there is a way I can easily import the old data into the new database so that everything lines up. So when the example is imported the CaseID across both tables is 1, but the SubjectID is 1 for John Smith and 2 for Jane Doe
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:41
Joined
Jan 23, 2006
Messages
15,364
I created a small list of facts that may be relevant to your set up.
A Case may involve 1 or many Subjects
A Subject is a Person
A Subject has a unique DOB
A Subject has 1 Gender
A Case has 1 StartDate
A Case may have 1 current Status
A Case may have 1 or many Status (Date related)
A Case may involve 1 or many Locations
A Case may have 0,1 or many Comments
A Comment has a Creator
A Comment has a DateCreated


I don't think it's a case of 1 table or 2. It's a matter of designing a database to support your requirement. Getting the structure right is critical to efficient database.
If your new database is not populated, then I see no possible breach of privacy.
You could populate a few records with "dummy data" --Porky Pig, Paige Turner.... Central City etc.

Often, when dealing with old, unnormalized structures, developers will bring the old data into a holding table, then use a variety of queries to select portions of the old data and insert same in the new tables.

Readers will need to see something,so I suggest you take a few records from the old database and change the names. It isn't the specific name that is important , it is the existence of a field value(s) to be reviewed/moved etc.

Good luck.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:41
Joined
Oct 29, 2018
Messages
21,358
Greetings,

At work I have inherited a database from a predecessor that is incredibly inefficient. In essence, it contains case information and subject information where there can be multiple subjects to one case. For example this is how it is entered:

Date Location Subject Name
1/1/2000 123 Main St 1. John Smith 2. Jane Doe

Subject DOB Gender
1. 1/1/1980 2. 1/1/1985 1. Male 2. Female


I already have the tables created in the new database with the proper ID fields and relationships defined. However, I am unsure if there is a way to easily import the old data. For instance, using the above example, I would need John Smith and Jane Doe to be classified under CaseID 1 but SubjectID 1 and 2, respectively.
Hi. In the above example, it looks like you might be able to match the data by the number assigned to them. However, even if you were able to automate this a bit using VBA, I still think you'll need a human eye to verify that you didn't miss anything or mixed things up. Good luck!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:41
Joined
Feb 19, 2002
Messages
42,976
When you are going from flat tables to normalized tables, you have to create the parent tables first and in this case, you are creating multiple rows per single input row. I would do this using VBA rather than a query. The logic will be easier to implement. You need a loop that parses the subject name and for each subject formats a new record. Inside that loop, you have to find the matching gender. Then write the row and move to the next subject.

Linking these to the rest of the data will be a little complicated since you are not going to be able to use a join. I think that to facilitate the conversion, I would temporarily store the unique ID of the source record in each row for the subject table. That should help when you are building the rest of the tables. Once the conversion is complete and validated, you can delete the now unnecessary column.
 

RogerCooper

Registered User.
Local time
Today, 13:41
Joined
Jul 30, 2014
Messages
277
Greetings,

Date Location Subject Name
1/1/2000 123 Main St 1. John Smith 2. Jane Doe

Subject DOB Gender
1. 1/1/1980 2. 1/1/1985 1. Male 2. Female

You are going to need the cleanup the data. To make this work as file you can import you will need to define a separator character that does not occur in the file itself. I would suggest using *. You can just use notepad to replace " 1. " with "*" and do that for the other numbers. You may able be able to do this for year itself, replacing "/2000 " with "/2000*". Then you can replace spaces with asterisks. The final step is to restore the spaces in the middle of addresses.
 

Users who are viewing this thread

Top Bottom