Transferring Records

[Calantha]

Hello

Is there an (easy) way of transferring records from a non-relational database to a relational database?

In the old database I have Inquiries, Applicants & Enrollments all in one database on one layout. In the new database I have Inquiries & Applicants as one and Enrollments as another. I have transferred the Inquiries & Applicants, as well I have transferred the Inquiry information for the enrollments, but now I need to get the extra information into the enrollments layout and have it match the inquiries layout.

How can I do this?

When I import it doesn't allow me to match related fields?

Thanks!

[T-Square]

It's a little tough to figure out your original structures and your new structures, but I'll take a stab. Tell me if this fits:

You get an Inquiry from someone, and they become an Applicant. An Applicant can have multiple Enrollments (as in multiple classes, say).

Your old file has fields for inquiry info, applicant info, and enrollment info. If you have an applicant with more than one enrollment, you repeat the applicant info in a new record with the correct enrollment data. Yes?

So in your new Applicant file, you've separated out the enrollment info into a new table, right?

Okay, if I'm right so far, you'll need to do the following:

Your Enrollment table probably has an Enrollment ID, but it will need an Applicant ID field. The original file does not have any Applicant ID.

First, you create your Inquiry/Applicant records, which generates a unique Applicant ID in the new table.

Next, you create a couple of calculated fields, one in Inquiry/Applicants and one in the old file, that concatenate the same fields and result in a unique match between the old and new files. You might use Last Name, First Name, and Inquiry Date. It doesn't much matter what you use--just that the result be unique for each applicant, and that the result in each file be the same. This calculation is only a temporary measure.

Now, create a calculated field in the original file that pulls in the Inquiry/Applicant ID number.

In the Enrollment file, your Enrollment ID should be set up to Autoenter some value. You could use the Serial Number feature, which generally works, although there are pitfalls to its use. Or you could create some other calculation.

Now, in order to create your new Enrollment records, all you need to do is import the original file's calculated Applicant ID into the appropriate Enrollment ApplicantID field along with any other field information applicable to the Enrollment.

Finally, Delete the relationships to the original file (from Inquiry/Applicant and from Enrollments) and the calculated field in Inquiry/Applicant that you used to join to the original file, and create a new link from Inquiry/Applicant ID to the App ID field in Enrollments.

HTH,

David