I currently have two tables;
1st Table has:
AccountNumber, Name, DOB, Branch
2nd table has;
Products: These fields are Yes/No fields as multiples can be selected
They include; cards, insurance, savings, loans, investment etc
I would like to generate the below report;
I can get a query to calculate age and calulate totals of a product, but as soon as I try to do multiple products in the query, it just wont work.
Can anyone help?
First query totals the responses but I can't get it to list the ages, second query will list the age and the product type, but I can only get it to list one product at a time, as soon as I list two it does not work;
SELECT Sum(IIf([Internet]=True,1,0)) AS InternetTotal, Sum(IIf([Redicard]=True,1,0)) AS RedicardTotal, Sum(IIf([Insurance]=True,1,0)) AS InsuranceTotal, Sum(IIf([Payroll]=True,1,0)) AS PayrollTotal, Sum(IIf([Phone]=True,1,0)) AS PhoneTotal
FROM newmships INNER JOIN Products ON newmships.MemberNumber = Products.MemberNumber;
SELECT newmships.MemberNumber, newmships.BirthDate, (Date()-[Birthdate])/365.25 AS Age, Count(Products.Internet) AS CountOfInternet
FROM newmships INNER JOIN Products ON newmships.MemberNumber = Products.MemberNumber
GROUP BY newmships.MemberNumber, newmships.BirthDate, (Date()-[Birthdate])/365.25