Results 1 to 3 of 3

Thread: Query Reports

  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: Query Reports

    Hi
    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;
    Age-----------0-10-----11-20----21-30---etc
    Products-------Totals
    Cards
    Insurance
    Savings
    Loans
    Etc

    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?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the SQL statement of the query you use and what error(s) occur(s) ("just wont work" is not very descriptive)?
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    2
    Hi

    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
    HAVING (((Count(Products.Internet))=Sum(IIf([Internet],1,0))));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •