Help Needed In Splitting Tables In Access

[SherryLynn]

Hello all,

It's been awhile since I have been here. I am in the process of creating a new DB on information that is coming from Excel. The data is getting converted over to Access permanently. I work for a child protection agency with children who go in and out of foster care. Currently there is an Excel document set up to track the foster family and the placements coming and going there, when the child is placed, by who, when they leave and why, etc. In the current set up the Excel document has multiple (duplicate) entries of both the foster families and the children (ie. the foster family takes in a number of different children, and a child could have been placed in several different foster homes). You see my dilemma and great need to move this antiquated system over. Anyway, it looks something like thisthis would be one row)

POS Home Address ChildPlaced DOB
SMITH Jane John 123 First St. JONES, Susy 3/12/99

Placed Discharged Reason Closed
2/15/05 12/1/05 Returned home 12/12/05

Now, if Susy and two of her siblings, Kyle and Tammy are placed at the same time, then three rows are created. The foster parents info is the same, but the child info changes (ie. their name, dob, discharge date, etc.). There are many other columns in the document, so this is a sampling of the information.

My question is - how can I split the tables so that I have a Foster Parent table with their identifying information and a unique ID (and only one record of them), a Child Table with their unique information and ID, and a Placement Table which I guess would link the two? And, am I on the right track? I am not really experienced with the Union queries, etc., which I am thinking I might need. Any help is most appreciated!

Sherry

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

A possible split-up could look like:

tblFosterParent
FosParID (autonumber,prim key)
FosParName
FosParEtc..

tblFosterChild
FosChildID (autonumber, prim key)
FosChildName
FosChildDob
FosChildEtc...

tblPlacement
PlacementID (autonumber, prim key)
FosChildID (foreign key)
FosParID (foreign key)
PlacementDate
DischargeDate
DischargeReason
Etc...

set the relations in the relationsview dragging and dropping the primary on the foreign keys. Set referential integraty (plus cascading updating).

Now you can build your forms and subforms and so on...

Pampers
There is only one way to change a diaper - fast

 

[SherryLynn]

Thank you both for your replies. I have to apologize though for not being more clear in what I needed help with. (It was clear in my head, but not in my writing it - haha). As you suggested Pampers, I would likely set up the tables that way once I have the data in Excel. However, my problem really lies in getting the data into the right tables when I bring it over. You see, if I just import and split the tables as is, I will have duplicate entries of the foster parents and their information. For each child placed in the home, another row is created with the same foster parent information, but the child info changes. So, for example:

FPS1 Child1
FPS1 Child2
FPS1 Child3
FPS1 Child4
FPS2 Child5
FPS2 Child6
FPS3 Child7

I don't know how to split the tables so that I have 1 foster parent record in the foster parent table and have it relate to all 4 of the children that were placed there. I guess this is where I really need the help. I hope I am explaining this right. Can anyone help?

 

[lespaul]

once you get the tables set up correctly you can use queries to extract the correct information from the excel sheets into the proper tables.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Pampers]

A way to reach this goal is to work with (temperary) update/delete/append query's. If you f.i. want to make a tblFosterParent. you create an empty tabel with the necessary field. Then create a update query where you update the tblFosterParent from your tblFosterChild or PlacementTable. Now you have a table with all the FosterParent names. Problably there are duplicate values; you can trace them them by making use of the Find Duplicate Value query Wizard. There are different ways to delete them. One way is to create another table where the FosterParentName-field property 'allow duplicates' is set to Not Allowed. Copying the values to this table will eliminate the duplicate value (as paste errors). Now you can link the tblFosterParents to another tabel on the FosterParentID-field. And so on... Hope this clears things up.

Once you have a non duplicate table

Pampers
There is only one way to change a diaper - fast

 

[SherryLynn]

Thank you Leslie and Pampers. I am going to give this a try and will let you know if I run into any trouble.

Sherry