Display DISTINCT Count on Report Header

[AccessHunter]

Hi,

Please help with this problem.

I have a report that displays a list of Case/Judge Records.
using the following fields,

Case Nbr, Case Seq Nbr, Judge Name, Attorney name

723187, 1 , Judge1 , Att1
723187, 2 , Judge1 , Att2
237484, 2 . Judge2 , Att1
237484, 3 , Judge3 ,Att3
756565, 1 , Judge3 ,Att4

The list is grouped first by the Judge Name and secondly by Attorney Name.
It has a page break by the Judge Name, records start on a new page on Attoeny Name change.

I need to display the distinct count of Case Nbr per Judge, here are the things I tried that did not work,

1) I made a textbox on judgeheader section, named it txtuniquerecs and the format event of judgeheader had the following code. This code is always returning 1 for every judge.

Private Sub GroupHeader0_Fo rmat(Cancel As Integer, FormatCount As Integer)
On Error Resume Next
Dim rst As DAO.Recordset
Dim strRecSource As String
Dim strSQL As String

strRecSource = Me.RecordSource
strSQL = "SELECT COUNT(*) FROM (SELECT DISTINCT casenbr FROM (" & strRecSource & ")); "
Set rst = CurrentDb.OpenR ecordset(strSQL , dbOpenDynaset)

If rst.BOF And rst.EOF Then
Me!txtUniquerec s = 0
Else
rst.MoveLast
End If
Me!txtUniquerec s = rst.recordcount
rst.Close
Set rst = Nothing
End Sub


2) Created a textbox named txtcasecnt with controlsource as =1 and runnigsum as 'Over All'. In the textbox in judgefooter section controlsource was set to =[txtcasecnt]. This was giving the count per judge but not distinct count.

Please help as this is so urgent.

Thanks a lot in advance

[ADezii]

Hi,

Please help with this problem.

I have a report that displays a list of Case/Judge Records.
using the following fields,

Case Nbr, Case Seq Nbr, Judge Name, Attorney name

723187, 1 , Judge1 , Att1
723187, 2 , Judge1 , Att2
237484, 2 . Judge2 , Att1
237484, 3 , Judge3 ,Att3
756565, 1 , Judge3 ,Att4

The list is grouped first by the Judge Name and secondly by Attorney Name.
It has a page break by the Judge Name, records start on a new page on Attoeny Name change.

I need to display the distinct count of Case Nbr per Judge, here are the things I tried that did not work,

1) I made a textbox on judgeheader section, named it txtuniquerecs and the format event of judgeheader had the following code. This code is always returning 1 for every judge.

Private Sub GroupHeader0_Fo rmat(Cancel As Integer, FormatCount As Integer)
On Error Resume Next
Dim rst As DAO.Recordset
Dim strRecSource As String
Dim strSQL As String

strRecSource = Me.RecordSource
strSQL = "SELECT COUNT(*) FROM (SELECT DISTINCT casenbr FROM (" & strRecSource & ")); "
Set rst = CurrentDb.OpenR ecordset(strSQL , dbOpenDynaset)

If rst.BOF And rst.EOF Then
Me!txtUniquerec s = 0
Else
rst.MoveLast
End If
Me!txtUniquerec s = rst.recordcount
rst.Close
Set rst = Nothing
End Sub


2) Created a textbox named txtcasecnt with controlsource as =1 and runnigsum as 'Over All'. In the textbox in judgefooter section controlsource was set to =[txtcasecnt]. This was giving the count per judge but not distinct count.

Please help as this is so urgent.

Thanks a lot in advance

1. Create a simple Query that returns the Unique (Distinct) combinations of Case Numbers (Case Nbr) and Judge Names and call it qryCases.
   

   SELECT DISTINCT <Your Table Name>.[Case Nbr], <Your Table Name>.[Judge Name] FROM <Your Table Name>;

2. Place an Unbound Text Box in the Judge Name Header Section.
3. Set its Control Source to:
   

   =DCount("*","qryCases","[Judge Name]='" & [Judge Name] & "'")

4. I'm sure the SQL Gurus will come up with a better solution, but for now, this approach will work.

[AccessHunter]

Thanks a lot, it worked.

Can I ask another favour please.

Your suggestion helped me to display the distinct number of case nbr records on the header..Thanks a ton for that.
Now the new requirement is to display those unique numbers per Judge on a summary page as the last page in the report. Is it possible to do that? Please advice.

Thanks

[ADezii]

Thanks a lot, it worked.

Can I ask another favour please.

Your suggestion helped me to display the distinct number of case nbr records on the header..Thanks a ton for that.
Now the new requirement is to display those unique numbers per Judge on a summary page as the last page in the report. Is it possible to do that? Please advice.

Thanks

1. Create a New Report and set its Record Source = qryCases.
2. Embed this Report as a Sub-Report into the Report's Footer.
3. Do not attempt to Bind this Sub-Report to main Report, it is independent.
4. Now, on the last page of the Report, (Report Footer), the Sub-Report will appear.

[AccessHunter]

Thanks for the immediate reply.

Actually What I wanted to do was to display the Unique count of Cases for each Judge on the summary page. When I tried the way you had explained, that displayed all the case number and judges like what I have in my qrycases.

Please help. Sorry if I am asking too much.

[ADezii]

Thanks for the immediate reply.

Actually What I wanted to do was to display the Unique count of Cases for each Judge on the summary page. When I tried the way you had explained, that displayed all the case number and judges like what I have in my qrycases.

Please help. Sorry if I am asking too much.

Set the Record Source of your Sub-Report to the following Query which in itself is based on qryCases.

SELECT qryCases.[Judge Name], Count(qryCases.[Case Nbr]) AS [Unique_Case_Count]
FROM qryCases
GROUP BY qryCases.[Judge Name];

[AccessHunter]

Thank you soooo much for helping me with the issues. You rock...
Thanks again.

[ADezii]

Thank you soooo much for helping me with the issues. You rock...
Thanks again.

You are quite welcome.