How do I calculate the amount of people in a query

[Angela M]

I am creating a database which shows the break down of an
applicants' family in a record using each person's gender
in up to 10 fields for up to 10 family members. (ie,
app1gender,app2gender, app3gender, and so on).I want the
database to work out how large the family is by adding up
all the males and all the females in those fields. Is it
possible to do this, and if so, how?

 

[Ed Warren]

Take a look at your data structure.

Sounds like you need a related table something like

TableApplicant
Applicant ID (key field) , Applicant Name, Applicant .......(other stuff
about applicant)

TableFamilyMember:
FamilyMemberID (key field), ApplicantID (foreign key , relating back to the
applicant), FamilyMemberGender, other stuff about family members.



Relationship: TableApplicant (1) --> ApplicantID--- > (m) TableFamily
Member

Now you can do a summary query that groups by applicantID and gender to get
a count of Males/Females and are not limited to 10 as you are in the
proposed datastructure.

---------------------------------------sample query

SELECT TableFamilyMember.ApplicantID, TableFamilyMember.FamilyMemberGender,
Count(TableFamilyMember.FamilyMemberID) AS CountOfFamilyMemberID
FROM TableApplicant INNER JOIN TableFamilyMember ON
TableApplicant.ApplicantID = TableFamilyMember.ApplicantID
GROUP BY TableFamilyMember.ApplicantID,
TableFamilyMember.FamilyMemberGender;


---------------------------------------end query

Ed Warren