Grouping individuals and collection information for families

[calbaptedm]

I'm building an attendance database for adults and children. I would like to know what the best way is to connect parents and spouses so that the information is displayed based on the family, not just the family name.
Currently I have set up a separate table for children and adults because their fields are different (i.e the table for children does not have a field for marital status). Ideally I would like the children's info form to appear based on how many children the parents say they have. I would also like to somehow group the children and parents or spouses together as a unit.
Any suggestions are greatly appreciated

[zmbd]

Please keep in mind that as we work thru this that Bytes is not a code writing nor homework service. Please read the FAQ and posting guidlines.


Good database design is the most difficult part of the process and it appears that you are already trying to use some of the normalization concepts and the like. So just a refresher on database design and normalization: A Tutorial for Access and a follow-up with Database Normalization and Table Structures.

given that there are a few dozen ways to do this, I'm going to borrow from a concept I used in a membership database because I think that is the same type of information you're after):

(note : tbl_* is a table name
[] denotes a field; () defines the field type; fields with "pk" in the nameare primary key fields for the table; fields with "fk" in the name are forgien keys refering to another table)

tbl_family
[family_pk](autonumber)
[family_name](text(25))
[family_otherinformation1](text/number etc)
[family_otherinformation2](text/number etc)
(etc..)

You really wouldn't need a seperate table for childeren and adults, you simply wouldn't need to enter data into the unrelated fields and you can design a form so that the unrelated fields are locked or even a seperate form; thus,

tbl_members
[member_pk](autonumber)
[member_name](text(25))
[member_fk_family](numeric(long) 1:M with tbl_family)
[member_otherinformation1](text/number etc...)
[member_otherinformation2](text/number etc...)
(etc...)

You could have a field that indicates child, spouse, etc... or maybe a seperate table that shows relationship between members and the relationship type (spouse, parent-father, parent-step-father, etc...)

tbl_relationship
[relationship_pk](autonumber)
[relationship_fk_family](numeric(long) 1:M with tbl_family)[relationship_fk_member_1](numeric(long) 1:M with tbl_member)
[relationship_fk_member_2](numeric(long) 1:M with tbl_member)
[relationship_relationship]((numeric(long) 1:M with tbl_type)

Then we need:
tbl_type:
[type_pk](autonumber)
[type_relationship_name](text(25))


So table tbl_relationship would have an entry for each member and the corresponding relationship
So If you have Family1 with Alpha and Beta are spouses and have child Delta you might have
(now of course I've used text here, the table would be all numeric)
(1)(family1)(alpha)(beta)(spouse)
(2)(family1)(beta)(alpha)(spouse)
(3)(family1)(alpha)(Delta)(Parent-Father)
(4)(family1)(beta)(Delta)(Parent-Step-Mother)

So,
With a simple select query, if you were interested in "Family1" then you you would find the PK in tbl_family and then do a select query against tbl_members where [member_fk_family] was equal to the PK from tbl_family. You could use that same basic query against tbl_relationship to pull that information.

Say you have a child (delta) you could look that child up in tbl_members, find the value for the family pk and then pull information from the other tables as to the family or find other siblings and so forth.

[NeoPa]

I would say that the layout outlined for you by Z is what you should be working from. It's not complete, but it is normalised and extensible (unlike most other attempts to handle what is essentially a very tricky setup). I must warn you that, though changing your design, and even using such a design, can be complicated and somewhat troublesome to get to grips with, it is nevertheless far more robust in the long-run, and apposite for your data. This latter point will prove to be very important the more you try to do with your data.