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

[bradp1979]

Hell again all,

I need to create a report that will count the number of times per employee ID that "yes" is in a specific column. I'd like the report to be sorted in descending order, with all the employees with 0 omitted.

The table is tblRelEventEmployee, and the column is MVR.

Thank you

[June7]

Is MVR a yes/no type field?

Maybe like:

SELECT EmployeeID, Count(*) AS CountOfYes FROM tblRelEventEmployee WHERE MVR = True GROUP BY EmployeeID ORDER BY Count(*) DESC;

[bradp1979]

Yes, MVR is a yes/no type field.

Where would I write that code... is that SQL when I'm doing report design?

[June7]

Yes, that is an SQL statement. Can be built with query designer. Either build a query object or open the designer from the report RecordSource property.

[bradp1979]

I tried to create a blank report, and I didn't have the option of selecting a record source. It just gave me the fields I could pull from. I've never created a report before, so this is all brand new information for me.

[bradp1979]

Actually, I figured it out. I created the query, then I made a report based on the query.

Now the one thing that I forgot to mention about it: I need it to be a monthly thing. So on September 1st, I want to pull just August 1st-31st, and so on each month.

[June7]

Options:

1. open the query object and enter filter criteria each month

2. build the query with dynamic input parameter popups - I NEVER do this

3. enter filter criteria into unbound textboxes and have the query reference the textboxes for input - similar to 2 - I also never do this

4. don't use a GROUP BY query object - base the report on raw data table and use report Sorting & Grouping design features with aggregate calcs in the report and group sections
enter filter criteria into unbound textboxes on form and have VBA code construct the filter criteria and apply to report when it is opened
this allows display of raw data and summary calculations
This is my preferred approach.

[bradp1979]

I'm afraid I don't understand how to use any of those options.

Here are some pictures that would be ideal. I just don't know how to do this:

Select MVR report from here and click "GO"



Then choose the date here:

[June7]

Here is example of VBA procedure to build filter criteria from user inputs on a form http://www.allenbrowne.com/ser-62.html

[bradp1979]

OK, so I've got everything set up how I want: I have the query and report created, and I have MVR report as an option on the switchboard shown above.

Now only 2 questions remain:
First, the table that the MVR information is coming from (tblRelEventEmployee) only has the employee ID, but I need it to also pull the employees name from tblEmployee. I tried adding the column of FirstName and LastName on tblRelEventEmployee, but I couldn't figure out how to make it pull the information. How can I go about doing this?

Second, I'm still fairly unclear as to how to create the option to only look at a specified date range.

[bradp1979]

Here's the code for the date selection. I copied and pasted it for another of the options that has date selection:

Option Compare Database
Option Explicit


Private Sub cmdGo_Click()
DoCmd.OpenReport "qryMVR", acViewPreview
End Sub


Private Sub Form_Current()
Me.txtBeginDate = Null
Me.txtEndDate = Null
End Sub


Private Sub Form_Open(Cancel As Integer)
Me.txtBeginDate = Null
Me.txtEndDate = Null
End Sub


Private Sub optgrpDate_AfterUpdate()
If Me.optgrpDate = 1 Then
Me.optgrpDate.Height = 900
Me.cmdCancel.Top = 1230.048
Me.cmdGo.Top = 1230.048
Me.Label1.Visible = False
Me.Label5.Visible = False
Me.txtBeginDate.Visible = False
Me.txtEndDate.Visible = False
Me.cmdGo.SetFocus
ElseIf Me.optgrpDate = 2 Then
Me.optgrpDate.Height = 1559.952
Me.cmdCancel.Top = 1875.024
Me.cmdGo.Top = 1875.024
Me.Label1.Visible = True
Me.Label5.Visible = True
Me.txtBeginDate.Visible = True
Me.txtEndDate.Visible = True
Me.txtBeginDate.SetFocus
End If
End Sub


However, it still just returns the same results, no matter what date range I put in there.

[June7]

Are you using option 4? Are you using the VBA method from the referenced link? There is no code that builds filter string. The filter string would be used like:

DoCmd.OpenReport "qryMVR", acViewPreview, , strWhere

qryMVR is name of report? Most developers would use a name like: rptMVR

[bradp1979]

qryMVR is a report. I mislabeled it since I built the query for the report first. I just named it the query, and didn't rename the report.

So to recap what I've done: I built query qryMVR, and the like-named report; I included "MVR report" on the reports switch board with the option to select a specific date range.

What I still need to do:
1. Make the date range work. Right now, regardless of what date range I enter, I get the aggregate count.
2. Include new columns on tblRelEventEmployee of FirstName and LastName, with the data pulling from tblEmployee based on their EmployeeID number. (tblRelEventEmployee only has employee IDs, whereas tblEmployee has both. I need tblRelEventEmployee to also have both)

[June7]

1. which of the options presented do you want to use?

2. no you don't - build a query that joins the two tables and use that as the report RecordSource

[bradp1979]

Seems like option 4 is the best option based on what I've already done.

I'm trying to build that query, but I'm not sure how to proceed. Should I use the wizard, or code?