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

[June7]

If still having problem, provide code or db for analysis.

[bradp1979]

Ok, I got the report to pull the employees first and last names from the other table, no problem

I just can't get it to only pull data from the selected date range at this point.

[bradp1979]

Code is in post 11 for the "on click" procedure after a date range is entered.

[June7]

None of which shows adaptation of code example from link referenced in post 9

[bradp1979]

True. I already have a form to select the date, which is what that post describes. My problem isn't that I can't make the date selection form, just that I'm having an issue with it working.

I am using the same code and the same date-selection form on another report, and it's working just fine. I just can't figure out why it's not on this one...I think it may have to do with the fact that the report I'm using isn't referencing the date the employee was selected as MVR...

[June7]

Certainly if data is not in report, can't filter on it.

Going in circles. Need to review report design and code - if you want to provide db.

[bradp1979]

FE and BE attached.3.06 Clean.zipCPV DB v.1.7 BE copy.zip

[June7]

Oh rats - THAT db!!

Since the report is based on an aggregate query and there is no date field in the query, have dynamic parameters in the aggregate query - the parameters would refer to controls on form for input - I NEVER use dynamic parameterized queries. I think this approach will be too inflexible for what you want.

SELECT tblRelEventEmployee.EmployeeID, Count(*) AS CountOfYes FROM tblRelEventEmployee
WHERE (((tblRelEventEmployee.MVR)=True) AND ((tblRelEventEmployee.ShiftDate) Between [enter start date] And [enter end date]))
GROUP BY tblRelEventEmployee.EmployeeID ORDER BY Count(*) DESC;

That takes us to the VBA approach from the Allen Browne example - code builds filter string and applies it to report when it opens.
DoCmd.OpenReport "qryMVR", acViewPreview, , strWhere

In this approach the report RecordSource would not be an aggregate query. If you want to list ALL employees even if they do not have related records in tblRelEventEmployee:

SELECT tblRelEventEmployee.*, tblEmployee.FirstName, tblEmployee.LastName
FROM tblEmployee LEFT JOIN tblRelEventEmployee ON tblEmployee.EmployeeID = tblRelEventEmployee.EmployeeID;

Now use report Sorting & Grouping features to set up grouping on EmployeeID and do the count in a textbox in group header/footer section: =Count(IIf([MVR]=True,1,0))
Use the VBA method to build filter criteria.

Why is MVR in both tables?

[bradp1979]

Oh rats - THAT db!!

Yes, sorry. I'd love to rebuild this thing from the bottom up, but that's still a bit beyond my skill.

Why is MVR in both tables?

I don't have an answer for that. When I removed it from tblEmployee, even though I thought I removed all relationships, I still was getting a missing parameter error.

After reading what you wrote, I still have no idea how to make the report only give me the information based on a specific date range. If you look at the main switchboard, and click "reports," and then select MVR Report and hit "Go," it will bring you to a date-selection form. However, regardless of what date range you enter, no dice.

I'm afraid nothing is any clearer than it was. I know that I have to include the date the MVR label was given on the query/report, but I am no closer to understanding how to do that or how to have it render the data I need.

[June7]

One step at a time.

Are you able to build the report as described? It's really a very basic functionality.

Work on dynamic filtering later.

However, at its simplest the code would be like:

Private Sub cmdGo_Click()
DoCmd.OpenReport "qryMVR", acViewPreview, , "ShiftDate BETWEEN #" & Me.txtBeginDate & "# AND #" & Me.txtEndDate & "#"
End Sub

Suggest you rename the report.

[bradp1979]

I haven't changed the report, I had planned on using the one that I had already created, with the addition of shift dates to be able to filter it to specific date ranges.

Also, I did rename the report to "rptMVR"

[June7]

You can't add the shift dates to the report because it is based on an aggregate query, unless you want to include the ShiftDate field in the GROUP BY clause and summarize data by ShiftDate.

So which way do you want to go:

1. dynamic parameters in aggregate query
or
2. report based on raw data and use report Sorting & Grouping with aggregate calc in textbox

[bradp1979]

Dynamic parameters in aggregate query. I don't want to confuse my users with additional text boxes. The ultimate goal here is for the user to select the report, enter a month, and see who was selected as MVR most for that particular month.

[June7]

What additional textboxes? Both methods use the same user inputs. What they don't see is how you use their inputs.

Did you try the dynamic parameters?

[bradp1979]

I didn't, because I don't know how to. I read the info at the link you supplied, and I wasn't able to reconcile it with what I'm doing in my mind. And at this point, I'll go with whatever method is the easiest to set up.