Splitting tables & Field

[bigal.nz]

Ok, I admit it.

I made a seriously bad design choice and have ended up with a flat table that now needs to be split.

Basically the incident table has fields to capture the basic details of a event:

Date
Time
Address
Incident Number

And on the same table:

Surname
Firstname
Address
Birth Date
etc

So at the moment if a person is involved in more than one incident there details are stored multiple times.

I want to now split this into two tables, table incident and table people.

I was wondering how I would go about this without having to manually re-enter data?

And would it likely break the DB? I recall reading something about views being a way to impliment the changes without breaking queries.

Cheers

-Al

 

[Isskint]

Do you have forms, queries and/or reports that reference the fields you want to split out? That would make things more tedious. I guess it comes down to the number of objects you would have to change.

The basic principle to split a table would be to run a Make Table query, adding all the fields you want to split out PLUS add the PK from the old table so you have a point of reference.
Run a query (or manually) to remove duplicates from the NEW table.
Add a field to the OLD table as the FK to the new table.
You can then run an update query on the old table to add the PK form the new table using the OLD PK as a point of reference.

With that done, it's just amend those objects!!

 

[bigal.nz]

Thanks for that. I see how you could split it out.

Could you not then create a view which allows your queries to see data in the same table structure its in now - thus avoiding the need to reedit all you objects/queries etc?

Cheers

-Al

 

[Isskint]

Not really sure what you are asking.

Your queries can already 'see' the data in the current table
Are you referring to duplicated data? So a query may return multiple rows of same data? If so use the DISTINCT or DISTINCTROW predicate in the query SQL.

 

[The_Doc_Man]

I think your follow-up question (to see the data as though the split had not happened) is answered by using a JOIN query between the two tables that are products of the originally described table-split operation.

 

[bigal.nz]

I think your follow-up question (to see the data as though the split had not happened) is answered by using a JOIN query between the two tables that are products of the originally described table-split operation.

Yes - that is what I am talking about it.

Do you have a good tutorial on this approach or able to offer any more detail?

 

[The_Doc_Man]

When you split a table, be sure to include some sort of prime key on one of the resultant tables so that you can do a join via Foreign Key from the other table. You have to decide which one will be the parent and which one will be the child (or perhaps you prefer the terms dependent and independent). Beyond that, almost any online article on JOINs should be helpful including many articles you could search in this forum. I don't have a specific place to point.