[Solved] Query

**b50** · 01-27-2011, 12:30 PM

I am working on creating a Query, and need help getting it to do some math.

In my table named Data and the column called Attended that has a series of Yes and No in a column on my table. What I need the query to do is count how many yes values there are, and count how many no values there are.

The final thing I need it to do is add up how many yes and no values there are then subtract all of the no values.

Any help would be greatly appreciated.

**alansidman** · 01-27-2011, 04:46 PM

There are probably several right answers to your question. One that comes to mind very quickly is to create two new fields in your query. In the first one type Yes: IIf([Yes/No field]="yes",1,0). In the second one, type No: IIF([yes/no field]="no',1,0). Now click on the sigma and for these two fields select Sum instead of Group By. Delete all other fields except the Yes/No field and make sure that one is set to group by.

Alan

**b50** · 01-27-2011, 04:55 PM

OK, that is helpful, but how to do I get the total minus the Nos?

One other thing I forgot to mention is there is also a date column, so the data is being organized by date. For exampel it is counting everything that is from 1/27/10

**alansidman** · 01-27-2011, 06:16 PM

Perhaps, it would be beneficial for you to post your db so that we can see all that you have and not try to guess at a solution. Make sure to remove any sensitive or confidential data.

**b50** · 01-27-2011, 06:29 PM

I can't really post it, because it is all sensitive data, but here is hopefully a more clear explanation of what I have and what I need:

Two of my columns are date and attended. Attended is a Yes/No, and the date is in date form. So for each date I need to group how many people attended on that date (all of the yes values for the given date), the number who did not attend (the no values for the given date). I then need to calculate how many people registered (yes plus no values).

Thanks.

**alansidman** · 01-27-2011, 08:56 PM

Ok. Look at the attached. You will need to have two queries. The first one determines the number of Yes and No by date. The second one then takes these and adds them together to give you the total registered. You only have to run the second one as it is dependent on the first. I did not know what version of access you are using so I made the example in 2002-2003 as LCD.

Alan