Need to create a report that tallies the "yes" answers in a column

[June7]

Which link - Allen Browne?

You won't learn if you don't try. Exactly what do you not understand? How to build dynamic parameterized query? Use the query designer. How to have dynamic parameters reference textbox? Again, the query designer can guide you.

What do you not understand about the instructions in post 23 (the one with an image of report design) and post 25?

[bradp1979]

What I didn't understand about post 23 is that, on the surface, it looks like the report I had already created. I wasn't sure what needed to be changed there.

Post 25, I tried to insert that code into the Go command, but I am getting a missing parameter of "ShiftDate"

[June7]

It is different because:

1. the RecordSource is not an aggregate query

2. the report has a grouping on EmployeeID - see the black bar that says "EmployeeID Header"

3. the aggregate calc is achieved by expression in textbox - see the CountOfYes label and the textbox below it

The code won't work on report as you designed it.

[bradp1979]

On the report imaged in post 23, did you simply rename the header to "EmployeeID Header," or is there something else I have to do to make it correct?

I know I'm asking a bunch of ignorant questions, I appreciate your patience. As should be completely obvious, I am no developer; I am simply the most technically-minded person in the office, so I got thrown into the "Database manager" position, regardless of my lack of knowledge.

[June7]

I had to create the EmployeeID group. Your report did not have grouping set up.

If you are using Access 2007, then the image I posted showing the Sorting & Grouping interface at the bottom is not applicable. Review https://support.office.com/en-us/art...6-C63CDDB2C7F9

If you want to use the dynamic parameterized aggregate query instead, then open the aggregate query object and edit it as described in post 23, except that [enter start date] and [enter end date] will instead reference controls on form.

Then your original code to open report should be good enough.

[bradp1979]

I set up the EmployeeID group, but now mine looks like this:

[bradp1979]

Grr. I've created a report that has the Employee ID grouping using the form wizard, but it is not even close to being correct. It's showing every single employee, and a # in the MVR column. That and the layout is very screwy. It's got the info from tblEmployee on one line, and the info from tblRelEventEmployee on another. so everything looks scattered and skewed.

Is what I'm hoping for even possible? A report that is rendered that would look something like this:

MVR count for August 1-31, 2015

Employee ID First Name Last Name MVR

__651________Bob______Roberts_____6
_1865________Bill ______Williams ___5
__845 ________Jill_______Bean______3

[June7]

As shown in my posted image, the data controls are moved into the group Header section, not the report Detail section.

[bradp1979]

Right. That's what I tried to do. This is what I've got.

[June7]

ALL data controls in the group Header - except ShiftDate as I did not include that in my example because I did not think you cared about raw data details, only the aggregate calc.

[bradp1979]

I do only care about the aggregate calc, but I thought since I want it to pull the report based on the date, ShiftDate would be necessary.

[bradp1979]

Ok, so I got all data controls in the group header:



However, when I switch to design view, I get this:

None of those people have a "Yes" in their "MVR" column.

[June7]

Okay, is the report going to be filtered to a single ShiftDate value? If so, include that textbox in the group Header as well.

And I am confused. Why does the Count() expression show "Yes" and not a number?

Try Sum() instead of Count().

What is the error the textbox has? Click on the little green triangle or the ! box.

[bradp1979]

I don't know if it's going to be filtered through a single shiftdate value. Each event, there is one employee who is selected as MVR. So I guess in order to make the count, it needs to see all events that fall within the date range, and count how many times each EmployeeID has "Yes" in the MVR column during that time.

[June7]

So unless you want to see all ShiftDate values, don't include that field on the report.