My real question is how I could then query a list of client relations (household) that could then be used possibly for a directory. For example;
Jimmy Smith Student
Jane Smith Mother
Jack Smith Father
Thank you for any help.
tblClients (all parents and children are entered here)
Client_ID
etc...
tblRelations (there is a one-to-many relation on the tblClients) One client could have many related client records. The client_ID of related client records is entered into a related_ID field of the tbRelations table.
Rel_ID
Client_ID
Related_ID
Relationship (mother, father etc...)I'd set it up as the following tables...
tblClients
ClientID int identity
ClientName varchar (30)tblRelationshipTypes
RelationshipTypeID int identity
RelationshipTypeName varchar(15)TblRelationships
ChildID int (stores the ClientID of the child)
ParentID int (stores the ClientID of the parent)
RelationshipTypeID int
You could then create the following view
SELECT Child.ClientID as ChildID,
Child.ClientName AS ChildName,
Parents.ClientName AS ParentName,
tblRelationshipTypes.RelationshipTypeName
FROM tblClients Child,
tblClients Parents,
tblRelationships,
tblRelationshipTypes
WHERE Child.ClientID = tblRelationships.ChildID AND
Parents.ClientID = tblRelationships.ParentID AND
tblRelationshipTypes.RelationshipTypeID = tblRelationships.RelationshipTypeID
which would return
ChildID ChildName ParentName RelationshipTypeName
---- ---------- ---------- -------
1 Joey Mrs. Smith Mother
1 Joey Mr. Smith Father
2 Susie Ms. Wilson Aunt
3 Billy Ms. Jones Mother
3 Billy Mr. Steves Father
No comments:
Post a Comment