This might be an easy one for the pros out there, but this is the first time I've had to deal with it. I expect there is a 1- or 2-step way to do this.
I have a data table (linked by ODBC) with eight fields, which hold the values "Y" or "N" (as text) for each record. The fieldnames are Q11_1, Q11_2, ... Q11_8. I'd like to end up with a cross-tab (or similar) that tallies the Y/N for each field, by each field, hopefully with the ability to add other criteria.
I'm sure you can see how setting up some intermediary queries will help make those data much more useful. I'm just not sure what is a simple approach to this.
The other fields are numerical so my head is stuck in that mode, and I have limited experience working with essentially binary data.
A crosstab will not suit your requirements. You could use a select query and total the fields Q11_1, Q11_2, ... Q11_8. Look up Help regarding Totals in query as you will require other fields for Group By.
Do you want to know the total Yes and total No for each field? What I mean is you may need a column called Q11_1_yes_tot for the total Yes for field Q11_1 and a column for Q11_1_no_tot for the total No for field Q11_1.
Then for the field of Q11_1 you would have the following
Q11_1_yes_tot:iif([Q11_1]="Y",1,0) now add a new column and enter this in the field Q11_1_no_tot:iif([Q11_1]="N",1,0)
You will need to repeat this for the other seven fields.
What are trying to achieve by totalling the fields?
Please attach a database with some sample data so that we can guide you.
Thanks, Poppa Smurf. Your suggestion basically gave me the change of direction I needed (towards the simpler!).
I created a single query using your formula style to get total "Y" and "N" for each Q and I seem to get the values I need. My purpose is to port this and other queries over to Excel to generate charts. The cross-tab idea, if achieved, would have made this slightly more tidy, but the select query is quite sufficient.