Yes/No Query & Crosstab

[leighms]

Hi
I have two tables, One lists client details (name,dob,branch they are allocated to etc) and another table on products they have purchased (this is a Yes/No table)
As the clients can choose multiple products I need to add up the "Yes" I have seen many articles on this but are having no luck, I can get it to add one of the fields but as soon as I add another it does not work.
My overall aim is to acheive a table as per below;
Age----------0-10-------11-20------21-30----Etc
Products-----Totals---
Cards
Insurance
Savings
Etc
So my questions are;
How do I create a query that will count "Yes" and how do I create a cross tab query to look like the above, when I try to do a cross tab query it only lets me enter 3 rows, I only want 1 row with a list of products, then the totals will appear under the age brackets

Regards

 

[vbaInet]

Can you show us (in a spreadsheet) some sample records from both tables, and what you would like to see.

 

[leighms]

This is how I want the end table to look like, the problem I have is the products in the rows are yes/no fields, as people can choose multiple products, so I need a query to first off count the "Yes" then I need to work out how to do a cross tab query that will put all the options in multiple rows;
0-1011-2021-3031-40Redicard025623Visa0037Payroll06312Internet04210Phone0000Insurance0002CCI0001Chq Book0001Savings42431841Loans0025Term Deposit1400Total438234102

 

[leighms]

sorry the format did not work below

-------------0-10--------11-20------21-30------31-40 etc
Cards
Insurance
Savings
Loans
Investment

So it is age of clients across the top and products they choose in the rows

 

[vbaInet]

That won't suffice. What is required to get a clearer picture was mentioned in my last post.

 

[leighms]

First table contains:
Account Number, Name, DOB, Branch
Second Table contains:
Account Number, then a list of products as above

I am trying to link these two tables via a query to see when they open an account what products they are purchasing, by age groups
I can get it to count the yes/no, for one product at a time, the problem is once I add multiple products to the query it just wont work

 

[vbaInet]

leighms,

Why aren't you following the example that I gave you in one of your threads?

http://www.access-programmers.co.uk/forums/showthread.php?t=216116

They are pretty much the same.

 

[leighms]

Hi
I have used the cross tab query, and it has been a huge help thanks, the problem I have with this table is that the items I need to put in the final table is they are Yes/No fields, and the previous table they are chosen from a drop down box.
So i have tried running a query to extract the Age and product, however when I add multiple products it does not work, if I use individual queries it does, however i have about 30 fields so I dont want to run 30 different queries.
Below are two queries I have come up with, the first one counts the products but I can't seem to add the age field, the second query works out the age and number of products but can only do one at a time;

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 Products.Redicard, newmships.BirthDate, Count("") AS expr1
FROM newmships INNER JOIN Products ON newmships.MemberNumber = Products.MemberNumber
GROUP BY Products.Redicard, newmships.BirthDate
HAVING (((Products.Redicard)=-1));

 

[vbaInet]

Let me see your db.

 

[leighms]

Attached

Please note the above queries are not in this database. I have a dummy one I work on to try things

 

[vbaInet]

It would be useful to know which tables/queries to look at.

 

[leighms]

Might help! Sorry
The main table is New Memberships (To calculate age)
The other table is NewMembershipsProducts
From these tables I need to produce the above report
Thanks for your help

 

[vbaInet]

I don't think I can help you there. Your tables are not normalized.

The process of achieving what you want is too cumbersome and would require a couple of Union queries. You need to sort out your tables.

 

[leighms]

thanks for your help, do you have any suggestions?