Count quandry in report

[meldrape]

Greetings,

I have a report based on a query. Since I have to track and display individual tasks per job number, the Job Number in the report produces duplicates which I suppress by not displaying them. The problem, however, is that I need a count of job numbers. How would I go about counting "distinct" job numbers? For instance, my september report should produce 89 jobs, but it shows 160 because it's not counting "distinct" job numbers. Any help would be greatly appreciated. Thanks in advance.

 

[PaulBricker]

Well here's the 'round the block' solution. Create a query using your Report Record Source and add just the Job Number. Turn on the Totals and Group By this field. Then create another query using this query. Add the JobNumber Field, turn on the Totals, and Count this field. Then on your report use a DLookup to get this value into your Report. Someone else may have a neater solution with a subquery of somekind but I couldn't get it to work with one query.

I also played with this code some but couldn't quite get the number I needed. Maybe it will spark something for you. In the Report Detail section Format Event I put this.

Static strHolder as String
Static i as Integer

If Me.JobNumber = strHolder
strHolder = Me.JobNumber
Else
strHolder = Me.JobNumber
i = i+1
End If
Debug.Print i

Basically each time the JobNumber changes i is incremented, but I think when you move to the next page, it runs the Format Event and changes the value of "i". I'm not sure if another event would help or not.

The last thing would be to Group By JobNumber and then add a textbox to the Group Header. Set the Control source to =1 and the Running Sum property to Over All. Set the Visible Property to No. This should give you a Count of the JobNumber. Then set a textbox to that value as your Count of JobNumber.

Paul

 

[PaulBricker]

On a little further investigation, if you look at the last suggestion, you don't actually have to have the JobNumber textbox in the JobNumber Group Header. You just need a textbox with the Running Sum value. You could size the textbox very small and shrink the Header section to virtually nothing and then follow the rest of the instructions. This will give you the count you are looking for.

Paul

 

[meldrape]

Thank you very much, Paul. I'll give it a try and let you know. Thanks again.