Suggestions on tieing tables together

[rgreene]

I am working on a database to be used with our Juvenile Detention Center and I'm running into a problem on how to tie the tables together. Here is what I have.

I have 6 main tables

Residents
Guardians
InvPersons
Admissions
Careplan
Discharge

My main Residents table stores data about the resident, Name, age, SS#, race ect..
Guardians stores info about the residents parents Name, address, phone...
InvPersons stores data about their Case worker, Attorney..

Those three table are all based on the resident.

Tables Admissions, Careplan, and Discharge are related to each individual admission. They are all linked together by a field called CaseNo. Each admission will have a seperate case #.
I had previously had the Resident and the Admissions table linked together by the name field. I had 1 field for the name. The Department wantes the name to be broken into 3 fields, Last, First and MI. Which kills my linking by name since there are several residents with the last name.
Each resident has a ResID that is assigned by the department. The problem there is I have to import data from a different database and there are several hundred residents with the same ResID so ResID is out. I thought about adding another ID field as autonumber to the Resident, Guardians, and InvPersons tables but I have learned that when a resident comes in they just add him/her into the resident table and then come back later (maybe even after adding in several more Residents) and filling in the rest of the tables. In my playing around I found that it was fairly easy to get the information added in the wrong order so that they don't always match up to who they are supposed to.
I then must tie in the Residents table to the Admissions table.

Any suggestions?

 

[DES]

You have to have an ID number of some sort in the "Residents" table to tie everything together with.

There is only one problem I foresee by doing so and that is if the Resident returns but that is another issue all together.

So if as you say they add the resident and then instead of adding all their info they just add another one the ID will still hold everything in place.

The case where you currently have a number of Residents with the same ID is a sample of bad setup and something will have to be done to straighten this out.

If you need more help or would like me to look at what you have you can get ahold of me at

technoweenie@sympatico.ca