Counting Multiple Field Contents

[CutAndPaste]

I've got several groups of fields, all in the same table where the responder can tick check boxes with yes/no answers e.g.
Type - Fruit (tick the ones you like)
x Oranges
x Apples
x Pears

Each Fruit type is a seperate field and they are only grouped into "Fruits" for ease of use for us humans. I have also got 1 other field that I need to report on, the gender of the respondent (can only be male / female). If I wanted to analyise my results where I was doing say gender against favourite fruit it'd be easy as they could just pick one fruit from a list and I'd do a cross tab, job done.

However the respondant can state that they like just oranges, or just apples and pears and not oranges etc. etc.

I'm wanting to count how many males and how many females like each fruit with the results laid out with the fruit types in rows and the total number of checks (-1s) for each of the fruit broken down by gender in columns. So the report would look like:

FruitName1 MaleCheckedTotal FemaleCheckedTotal Fruit1TotalChecked
FruitName2 MaleCheckedTotal FemaleCheckedTotal Fruit2TotalChecked
FruitName3 MaleCheckedTotal FemaleCheckedTotal Fruit3TotalChecked
etc. etc.
(not sure how this is going to look on the web page but there is 3 rows of fruit and 4 columns...)

I think I'm limted to 3 rows in a cross-tab so I'm not sure how to go about this as some of my questions go to 15 options. I'm not sure whether this is primarily a querying problem or some nifty reporting methods are needed...

Any ideas?

 

[raskew]

If tblFruit looked like this: (the fruit fields are actually yes/no check boxes)

Gender Apples Oranges Bananas
M Yes No Yes
F Yes Yes No
F Yes No No
M No Yes Yes
M Yes No No
F Yes No No


This totals query:

SELECT tblFruit.Gender, Abs(Sum([Apples])) AS LikeApples, Abs(Sum([oranges])) AS LikeOranges, Abs(Sum([Bananas])) AS LikeBananas
FROM tblFruit
GROUP BY tblFruit.Gender;

Would result in:

Gender LikeApples LikeOranges LikeBananas
F 3 1 0
M 2 1 2


Does that help?

 

[JoysTick]

I've got (what I think) is a similar problem -

I've got a table [tblClients] and another table [tblClientVisits] linked to the client table by a foreign key [ClientNumber]
In the [tblClientVisits] table I've got a large number of Yes/No Question fields. I'm selecting groups of Clients in a query by [Gender] and [VisitDate] and so a Client may have none, one or many visits in the period.

I'm trying to find out how many Unique Male clients and Female clients answered Yes to each question. Whether or not they answered yes once or five times on single or multiple visits is not important. As there are quite a few questions I'm not wanting to set up a query for each of them. I've got a report based on a query that counts how many times each Yes/No was ticked, but was thinking that it may be better to do the count of unique Clients for each of these within a report.

In the report, I think I'd try to get something like;
DCount("[MyTable]![MyYesNoField]",[MyTable]","Something here to count unique client numbers that answered yes to the question")

but I am not sure of the code to do the last bit!

(http://www.access-programmers.co.uk/ubb/Forum5/HTML/002149.html)

Any answers happily received! ;-)