Member Avatar for cryzone77

hi! i am making a nso system(a citizen record system w/ ms access as database)

i dont know how to make it work:

there is a citizen, then under the citizen there is subdatasheets namely:

father, mother, sibling 1-5.

all i managed to do is have ONE subdata sheet under citizen.

please help me.. tomorrow's our thesis defense and i cant find any other help.

NSO_database.zip (54.38 KB)
Edited by cryzone77 because: n/a
  • 2 Contributors
  • 7 Replies
  • 118 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

As described, the goal is one citizen record with separate, clearly scoped parent and sibling lists. was right to push away separate Father/Mother tables — that creates duplication and maintenance headaches. A cleaner pattern is to keep all people in one Person table and store relationships in a small, separate Relationship table that links two Person rows and records the relationship type (father, mother, sibling, etc.). That design scales beyond five siblings and keeps each parent exclusive to the child when you enforce the right constraints.

In Microsoft Access, avoid trying to make the table datasheet show all related lists at once: the table subdatasheet mechanism is limited. Build a single main form for the citizen and add one or more subforms (datasheet-style) to show related people. Each subform should be based on a query that joins the Relationship table to the Person table so it displays the related person details. Use the subform property Link Master Fields / Link Child Fields to bind the subform to the current main record. For father and mother you can either use two small subforms filtered by relationship type, or one subform with a type filter and different displays.

Data-integrity and UI tips: enforce referential integrity on the Relationship foreign keys, add a composite unique index on (Person, RelationshipType) if you must prevent multiple "father" rows for a single child, and consider creating reciprocal relationship rows automatically (for example adding a "child" link when a "father" is entered) via a macro or AfterInsert code. Use combo boxes in your relationship subforms to pick an existing Person rather than entering names, which prevents duplicate person records. For the thesis demo, present the main form with three visible subforms (father, mother, siblings) so the examiner sees the relationship model and how it prevents mixing records.

Recommended Answers

All 7 Replies

Member Avatar for debasisdas

why you need a separate table for father, mother, sibling.

Are not they citizens ?

You need to store the info of all the citizen in a single table and then map the data to proper column.

Member Avatar for cryzone77

why you need a separate table for father, mother, sibling.

Are not they citizens ?

You need to store the info of all the citizen in a single table and then map the data to proper column.

yes they are citizens, however, i would like to see them in such a way that is organized. what i mean is this ... for example citizen1 has father1 and mother1.. if i add a new citizen named citizen2, father1 and mother1 will WILL BE EXCLUSIVELY for citizen 1 only..

they wont get mixed up with citizen2's parents or to any other citizen and so on.

Member Avatar for debasisdas

suppose you want to store father information with all other data of citizen.
You need to add a column say father_id to citizen table.

like

citizen
citizen_id,
citizen_name,
citizen_add1,
citizen_add2,
.............
.............
father_id,
.............

while storing data , you need to store the citizen id for the father into the fathers_id of the citizen. Sounds good ?

Member Avatar for cryzone77

suppose you want to store father information with all other data of citizen.
You need to add a column say father_id to citizen table.

like

citizen
citizen_id,
citizen_name,
citizen_add1,
citizen_add2,
.............
.............
father_id,
.............

while storing data , you need to store the citizen id for the father into the fathers_id of the citizen. Sounds good ?

actually what i meant was the tblFather IS in one to many relationship with CitizenID in tblCitizens

and so does tblMother and so on....

so the record of tblCitizen will have "7" subdatasheets showing the data stored in FatherName...
MotherName... and so on.
^_^

Member Avatar for debasisdas

let me explain clearly.

Every father is a son, and is a citizen, right.

so creating a separate table for father and another one for mother won't serve any purpose.

All citizen irrespective of father , mother, son need to be stored in a single CITIZEN table.

Member Avatar for cryzone77

let me explain clearly.

Every father is a son, and is a citizen, right.

so creating a separate table for father and another one for mother won't serve any purpose.

All citizen irrespective of father , mother, son need to be stored in a single CITIZEN table.

ok... soo... how do you do that?

Member Avatar for debasisdas

as i said you need to have a father_id column in citizen table.

Edited by debasisdas because: typo
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.