Table structure creation

[jsampath7]

I want to create ms access database for church

In this database I have to store the memeber details and family details of the member


for eg:

father1 is member, he had three children

son1
son2
daughter1

if son1 got married then he will be member

so,
here the problem is need to store all the details in one table
based on the relationship need to show data


how can I do that

 

[boblarson]

Check out the data models here:
http://www.databaseanswers.org/data_models/

Look at category 7 (Churches).

 

[jsampath7]

Thank you boblarson.

 

[CJ_London]

Try this table structure

TblMembers
 
ID as autonumber
MemberName as text
RelationID as long
Relation Type as text
 
 
And the data would be
 
ID      MemberName      RelationID     RelationType
1       Father1             0                 ""
2       Son1                 1                Son
3       Son2                 1                Son
4       Daughter1          1                Daughter
5       Father2             0                ""
6       Son1                 5                Son
 
 
To show the relationships the query would be
 
Select tblMembers.membername as Father, tblmembers_1.membername as childname, tblmember_1.relationtype as relationship from tblmembers left join on tble_members_1 on tblmembers.id=tblmembers_1.relationid

 

[jsampath7]

Thank you CJ

but the problem is that, when I want to show the father1 details along with the family details in subform

 

[CJ_London]

Not a problem - here is the same thing slightly revised.

In the code TblMembers is the Father details and TblMember_1 is the childs details

- give it a try

TblMembers

ID as autonumber
MemberName as text
[COLOR=red]Detail1 as text[/COLOR]
[COLOR=red]Detail2 as text[/COLOR]
RelationID as long
Relation Type as text


And the data would be

ID      MemberName      RelationID     RelationType
1       Father1             0                 ""
2       Son1                 1                Son
3       Son2                 1                Son
4       Daughter1          1                Daughter
5       Father2             0                ""
6       Son1                 5                Son


To show the relationships the query would be

Select tblMembers.membername as Father, [COLOR=red]tblMembers.Detail1, tblMembers.Detail2,[/COLOR] tblmembers_1.membername as childname, tblmember_1.relationtype as relationship, [COLOR=#ff0000]tblMembers_1.Detail1, tblMembers_1.Detail2,[/COLOR]  from tblmembers left join on tble_members_1 on tblmembers.id=tblmembers_1.relationid