Counting multiple Yes/No fields

[Charlottew14]

Hi,

I'm in a bit of a fix with this one, and due to my lack of knowledge about VBA/SQL I'm stuck using the query functions which Access 2010 has given me to play with.

I have a table, which is being populated by a number of users. The table has 211 rows (customer field - this is a fixed amount) and there are 5 other columns (information which might or might not be available for that customer) which are all yes/no fields. Each row may could have any combination of ticks in those 5 columns.

I have a form to fill out this information, but now I need to use the data.

I'm trying to create one query which counts how many of each field are populated. I need it to give me the total of each of the "information" columns, and another to give me a list of all of the customers which have at least one of the information columns ticked.

I will also need to be able to see how many customers have all of the information columns ticked, and how many have none.

I know this won't just be one query, but whenever I try and find out how to do it it tells me to do the query in VBL and I haven't the faintest idea how to do that!!

Thank you so much for any help

 

[MSAccessRookie]

If you consider the fact that each of the Y/N Fields has one of two values (Yes/No or True/False), then using an IIf() Statement could turn the situation into a simple Math Process.

IIf(YNStatement1=True, 1, 0) + IIf(YNStatement2=True, 1, 0) + IIf(YNStatement3=True, 1, 0) + IIf(YNStatement4=True, 1, 0) + IIf(YNStatement5=True, 1, 0)

Notice that the result of each IIf() will be either 1 or 0, and the sum of all will give you the total number of boxes checked for a user.

You can also build a totals Query that sums the value for each Field to get the number of users selecting each Field if you want or need that information.

Try it and see

-- Rookie