Count Total Distinct Records on Report

[DaveyEdgar]

Hi all!
O.K. heres what I got and what I tried from the other similar posts' solutions.

TblEmployees
-employeeID
-name

TblEvaluations
-evalID
-employeeID
-evaluator
-evalDate

TblQuestions (a total of 10 questions/records)
-questionID
-question
-answer

So 1 employee can have many evaluations. The evaluation form is populated with the questions by a command button.

The queryevaluations has the above fields in it.

The Report:
EmployeeID Group Header / Ascending
EvalDate Group (no header or footer) / Descending
EvalID Group Header / Ascending
Nothing in the Detail Section
The EvalID Footer has just the total score.

I want a text field at the Report Header that will count distinctly the amount of evaluations total. For example If there is a total of 4 evaluations done, my "=count(evalID)" control source should say "4". However, it says "40", because the query includes 10 questions/records for each evaluation.

I tried this...
1. Make an additional Sort/Group on evalID
2. In the evalID footer, put a tiny text box. Make its control source =1, and Running Sum Over Group. Let's call it txtevalID
3. Make the evalID footer small, and set Visible to No.
4. In the Report Header, put a text box that has its control source as txtevalID
...but the text box in the header always shows "1"

I tried this...
You could try an unbound text box with its control source set to =Sum(Abs(evaluationID))
... but it shows "#Error"

Any ideas on how I can make this work? I apologize if my explanation is confusing ;-P
Thanks a million for your help!

(This is 'Onliner' by the way, for some reason that account isn't working)

 

[sdk]

Hi

To get this into the header, you might need to explicity query the dataset and retrieve your value. This might get complicated: copy a clone of the report recordset and loop through counting each chnage in the group spelling (the recordset must be ordered by you group to achieve the desired outcome), then set a value on the report.

Alternatively, put your counter into the group header (ie no group footer if you don't want one) and have a control in the report footer that points to the the counter (ie source: "= txtCounter"). This will show the last value of the source, is easy, adaptable, BUT can only be used in the footer (it can't work in the header because the groups don't exist at that time).

Another alternative is if the source qry can include a count of the number of groups (through a subqry etc), and then have a report control point to it - this can go anywhere, but might excessively complicate the query.

Cheers

S

 

[THWatson]

Davey
=Sum(Abs(evaluationID)) shows #Error because you have no field "evaluationID" even though that is what you called it in the other post.

Rather, you have "evalID"

You could try =Sum(Abs(evalID)) but I suspect you might need to put it in the Report Footer rather than the Header.

Tom

 

[dhookom]

Why not just use a totals query as the record source for your report? You don't show the detail section so a totals query should work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DaveyEdgar]

Hi all thanks for your responses I greatly appreciate it. I was away and am now back.

Here are the results

You could try =Sum(Abs(evalID)) but I suspect you might need to put it in the Report Footer rather than the Header.

Thanks watson I checked the spelling and corrected it. I put it in the footer but am getting erroneous counts like 689 and 2277 etc.

Why not just use a totals query as the record source for your report? You don't show the detail section so a totals query should work.

Thanks hookum but unfortunately this report serves several functions. I used this "

http://www.tek-tips.com/faqs.cfm?pid=181&fid=5497"

so I can select the criteria for the report from forms and list boxes etc. so I need to have a distinct count of the returned records on the report according to the criteria chosen. So the underlying query has to have all the records in it including the questions and their answers.

To get this into the header, you might need to explicity query the dataset and retrieve your value. This might get complicated: copy a clone of the report recordset and loop through counting each chnage in the group spelling (the recordset must be ordered by you group to achieve the desired outcome), then set a value on the report.

Thanks sdk but that is a bit beyond me unless there is a downloadable sample somewhere

Alternatively, put your counter into the group header (ie no group footer if you don't want one) and have a control in the report footer that points to the the counter (ie source: "= txtCounter"). This will show the last value of the source, is easy, adaptable, BUT can only be used in the footer (it can't work in the header because the groups don't exist at that time).

May I ask a favor sdk? Can you elaborate on this one a little? What I did is put the txtevalid text box with control source =1 in the group header, and a text box in the report footer with its control source txtevalid but when I run the report i get a dialog box promtimg me to enter data for the txtevalid field. I assume it is because as you mentioned the data for the txtevalid field doesn't exist yet because the report hasn't been run yet.

The subquery may not work since I need to count the distinct records of the results of the criteria chosen on the form... oy!

?? How can I get around this? Is there any kind of workaround or mickeymouse way of getting it to work?