Dcount, 2x criteria - counting family members under 16years

[jamierbooth]

Hi Guys. - Using MS Access 2000

I may be being a bit over-ambitious here. I'm building a customer management database. Part of which is a table to record details of all members of the main customers family or the household compliment.

I'm currently using a DCount to work out how many people on the table are related to my client, to work out the size of the family.....

=DCount("MemberID","tblHHMembers","CustID = [CustID]")+1

Could I also then count the members of the household that are under 16 years old by looking at the DoB from the same household members table?

I realise that I'd need to ask access to calculate age at the same time as working out if they're under 16 and then count them if they have the same Customer ID - which might need a little more than this single function!

Cheers.
Jamie.

 

[plog]

At this point I think you build a query, then use the 2 Dlookups into that, or build a sub-form based on it. The query would tell you both the total family members and the family members under 16. It would look something like this:

SELECT CustID, (COUNT(MemberID) + 1) AS FamilySize, SUM(If(getAge([MemberDOB])<16, 1,0)) AS FamilySizeUnder16 
FROMtblHHMembers
GROUP BY CustID

A few notes: I made up the function getAge() in the above code. It doesn't exist, until you build it. It's pretty standard code that would take a birthdate and return the age of the person based on today's date. It's something you would need to build in a Module. Also, I guessed on the name of the DOB field, be sure to update that with the actual field you have.

 

[CJ_London]

or you can use the datediff function

SELECT CustID, (COUNT(MemberID) + 1) AS FamilySize, abs(SUM(datediff("yyyy",DOB,Date())<16)) AS FamilySizeUnder16 
FROMtblHHMembers
GROUP BY CustID

The abs function will convert the true (-1) to positive