Creating 2 related tables from 1 large “flat” table ...

[optimist 2]

I have a client who created a Filemaker database back in the early 90s having over 12,000 records in one table (“Master”) that she populated with patients having a unique account number (actually a text field) consisting of numbers beginning with 100 and increasing incrementally by 1. Each account number is followed by a letter suffix. The first time a patient came to the clinic he/she was assigned an account number, e.g., 2476 a The next visit by the same patient was identified with the account number 2476 b, the third visit 2476 c, and so on. Each of these records duplicated the same demographic data, i.e., name, address, telephone number, etc, instead of the currently conventional use of related tables - a contact table and a clinic visit table.

My job is to create two related tables form the “Master” table:

1. A “contact” table having demographic information for each patient

2. A “clinic” table getting demographic data from the “contact table” but which stores records for each visit the patient makes to the clinic.

The unique data currently stored in the “Master” table are in fields as follows:

1. account number 2476 a

2. account number prime 2476 (the account number stripped of its letter suffix)

3. visit date

What would be the easiest way to create the two related tables without losing data?

I am using Filemaker Pro Advanced 10.0v3

[comment]

In the Patients table, set the Account field's validation to Unique, Validate Always. Then import all records from the Master table, mapping Master::account number prime -> Patients::Account. Next, import from Master into Visits table, mapping Master::account number prime -> Visits::Account. Relate the two tables matching on Account.

Note:

This is just the first step. You should look for inconsistencies in the data (which are likely to be found with the previous setup) and fix them. Don't forget to set Patients::Account to auto-enter a serial, starting from where the old system left off - though personally, I'd renumber the patients (using a new field) and switch the relationship to use the new numbers.

[optimist 2]

Thank you so much. I will try this when I return on Monday.

[optimist 2]

I did the imports as you described. However, this results in two identical files, each having duplicated patient demographic data, just like the Master

The objective is to have one file, "Patients" containing only one record for each patient; and the other file, "Clinic Visits" link to "Patients" to retrieve demographic data for each subsequent clinic visit.

The existing “Master” file has records for every clinic visit, but patient demographic data is duplicated in each record, a flat database if you will. Records for each patient are uniquely identified in the “Account Number” field: e.g., "3498 a" the first visit, "3498 b" the second visit, etc. These are text fields.

Account Number Prime is a calculation field (number field) “LeftWords(Account Number; 1)” that allows easy searching for all visits made by each patient.

My challenge is to migrate all data in “Master” to two tables, I guess in a one to many relationship, i.e., one patient but many clinic visits.

Thanks for the help.

[comment]

If the Account field in the Patients table is validated as Unique, Validate Always, then only unique records will be imported.