Need a total count of "Yes" entries for records within a specified date range.

[DonO]

I have a working(two table) database (Access 2007) consisting of records with 78 Yes/No/N/A entries. Using a date range as criteria, the database returns all records within the range. I would like to get a count of "Yes" entries for each record and a count of total entries for each record to be used for a percentage claculation. I can currently export the data to an Xcel Spreadsheet and perform the calculations myself but think it would be mre streamlined to complete all the functions within Access. This is the most in depth I have ever gotten with Access and am floundering. All suggestions appreciated.

[ItsMe]

You can create, name and save query objects with Access. Use the Query Builder via the "Query Design" option under the Create tab in the Ribbon. While in design view of your new query, use the Totals option towards the Right of the ribbon. Now, towards the bottom of the window, there is a grid and the grid will have a field for Totals. There is a list of options available to select a totals function for a given field/column in your query. Default for the Totals is "Group By".

[pbaldy]

Sounds like you're saying that there are 78 fields? If so, not a normalized design and harder to work with. You'd have to add them up like:

IIf(Field1="Yes", 1, 0) + IIf(Field2="Yes", 1, 0) +...

[DonO]

That is exactly what I have done and the result is a '1' for every "Yes" entry. What kind of query could I build to give me a total of all the "1's"?

[DonO]

I guess I had better start looking at building a new database. I did not build this one, just trying to work with what was handed to me.

[pbaldy]

I would certainly consider it. If you stay with it, what exactly did you do that resulted in a '1' for every yes? What I posted should result in the total.

[DonO]

Paul,
In the Expression(s) I wrote, I used [] around the Field ID i.e. IIf([Question1]="Yes","1","0") + ...

[DonO]

Using exactly what you provided, I got a result of 0. That was: IIf(Question 1="Yes", 1, 0) +IIf(Question 2="Yes",1, 0) + IIf(Question 3="Yes", 1 , 0)....

[pbaldy]

The brackets would be required if the field has inadvisable spaces in it. You don't want quotes around the 1 and 0 though. That makes them text instead of numbers.

IIf([Question1]="Yes",1,0)

[DonO]

Finally ended up with Sum(IIf([Question 1]="Yes",1,0)+IIf([Question2]="Yes",1,0) +....IIf([Question78]="Yes",1,0)) AS Positives, 78 AS Total and then anothr major string ending with the criteria for start and end date. It works. Been looking at building a new datatbase and this normalization thing would be a few more table and not nearly as many fields!!!

[DonO]

Thank You for your help! We'll mark this as solved!

[pbaldy]

No problemo!