Add two records to create one

[Tina Hudson]

Good afternoon,

I am working on a system to track the location of children in custody. I
have a table that contains all the people (tblPerson) in the system. I have
other tables for just child info (tblChild) and family member (tblFamily)
info. I also have a table to keep track of their location, including the
data for each change in location (tblLocation). Each of these tables is
properly related (I think!) to the other, by way of "Family_ID". I also have
fields identifying their relationship, i.e., grandmother, grandfather, etc.
The forms to establish relationships work great.

The child and family member info are entered before a location can be
entered.

Here is my quandry: (1) a caretaker can either be a 2 parent household or
single parent household, (2) made up of family members or non-family members.
Currently, I am tracking the non-family members in tblFamily (but I really
don't like it here since they aren't family, and these non-family membes can
end up being caretakers for more than one family and I want to be able to
have on a report the names of the children for each caretaker household).

Okay, to solve (1) I think I should be able to concatenate 2 records into a
single row for the user to select in a combo box. Can I just check for
duplicates in "Add1" and "city" and then combine them as one record? Should
I then store this record in a new table (tblProvider)? Any suggestions?

For (2), the user is going to have to enter info for the non-family member
caretaker (with this info being stored in another table (tblProvider)), and
not relate this caretaker to any specific family. Then, somehow get this new
caretaker info to show up in the combo box (along with all the other possible
non-family caretakers, is what I was thinking). I could have a bound or
unbound control for the user to select to identify the careaker as either
"relative" or "non-relative" and depending upon what the user chose, the
appropriate list of names would appear in the combo box. I can have the user
enter non-family member info as a "household, rather than separate names for
both male and female caretakers.

Well, I hope this is challenging enough. Thanks in advance for any help or
suggestions.

 

[John W. Vinson]

Good afternoon,

I am working on a system to track the location of children in custody. I
have a table that contains all the people (tblPerson) in the system. I have
other tables for just child info (tblChild) and family member (tblFamily)
info. I also have a table to keep track of their location, including the
data for each change in location (tblLocation). Each of these tables is
properly related (I think!) to the other, by way of "Family_ID". I also have
fields identifying their relationship, i.e., grandmother, grandfather, etc.
The forms to establish relationships work great.

Ummmm... That doesn't sound right. A Family - I presume - does not have a
location. A Child has a location; and it's surely possible that there could be
two or more children in a family, and that those children might be in
different locations!

Don't get so hung up on the idea of "Family_ID ties everything together" that
you make it impossible for your database to reflect the real life situation!

The child and family member info are entered before a location can be
entered.

Here is my quandry: (1) a caretaker can either be a 2 parent household or
single parent household, (2) made up of family members or non-family members.

Or a family member and two non-family members, or an extended family, or a
wide assortment of other possibilities... again, your database should reflect
the real life situation, not vice versa.

Currently, I am tracking the non-family members in tblFamily (but I really
don't like it here since they aren't family, and these non-family membes can
end up being caretakers for more than one family and I want to be able to
have on a report the names of the children for each caretaker household).

So don't call it tblFamily unless it's a family. Call it tblGuardians or
tblCaretakers and have it reflect the real life situation.

Okay, to solve (1) I think I should be able to concatenate 2 records into a
single row for the user to select in a combo box. Can I just check for
duplicates in "Add1" and "city" and then combine them as one record? Should
I then store this record in a new table (tblProvider)? Any suggestions?

OW.....

No. You're going about it wrong. Storing multiple two (three? four?) records
in one record is NOT the way to go about this!!!

For (2), the user is going to have to enter info for the non-family member
caretaker (with this info being stored in another table (tblProvider)), and
not relate this caretaker to any specific family. Then, somehow get this new
caretaker info to show up in the combo box (along with all the other possible
non-family caretakers, is what I was thinking). I could have a bound or
unbound control for the user to select to identify the careaker as either
"relative" or "non-relative" and depending upon what the user chose, the
appropriate list of names would appear in the combo box. I can have the user
enter non-family member info as a "household, rather than separate names for
both male and female caretakers.

Well, I hope this is challenging enough. Thanks in advance for any help or
suggestions.

How about:

tblHouseholds
HouseholdID <Primary Key>
Address1
Address2 <e.g. "Apt. 3" or "Lot 5">
Direction
Street
Suffix <e.g. "Ave.", "St.", "Place">
City
Postcode
State

tblPeople
PersonID <Primary Key>
LastName
FirstName
DOB
<other biographical data as appropriate>

tblChildHistory
PersonID <link to tblPeople, Primary Key>
HouseholdID <link to tblHouseholds, where the child is now>
StartDate <when they came into this household>
EndDate <when they left; NULL if they're still there>
<other info about this child's stay in this household>

tblCaretakers
PersonID <Primary Key, link to tblPeople>
HouseholdID <link to Households>
Role <e.g. Primary caretaker, parent, grandparent, paid provider, ...>
<other info about this person's role in this household>




John W. Vinson [MVP]

 

[Tina Hudson]

John,

Thanks! I came to the same realization late yesterday regarding the table
structures. However, I don't think I made myself totally clear in the first
paragraph, so let me try to further explain. I'm actually not associating a
family with a location, just each child with the safety resource in which
they are placed. This safety resource is either with a family member(s) or
another interested person we've qualified and approved. The purpose of the
safety resource is to prevent the child from entering foster care and the
safety resource placement is for only a short time, theorectically.

This DB is to help us better track the experiences (i.e., length of stay,
outcome) of a child in a safety resource, as well as help us identify best
practices.

I had already set up most of the tables you recommended, just not the
tblHouseholds, which is what I figured I needed yesterday. However, I wasn't
exactly sure how I was going to set that up, so thanks!

And, thanks for taking the time to read the long email.

Have a good one.