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_Format(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.OpenRecordset(strSQL, dbOpenDynaset)

If rst.BOF And rst.EOF Then
Me!txtUniquerecs = 0
Else
rst.MoveLast
End If
Me!txtUniquerecs = 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

Dec 10 '07 #1

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_Format(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.OpenRecordset(strSQL, dbOpenDynaset)

If rst.BOF And rst.EOF Then
Me!txtUniquerecs = 0
Else
rst.MoveLast
End If
Me!txtUniquerecs = 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.
   
   Expand|Select|Wrap|Line Numbers

   1. 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:
   
   Expand|Select|Wrap|Line Numbers

   1. =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.

Dec 10 '07 #2

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

Dec 10 '07 #3

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.

Dec 10 '07 #4

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.

Dec 10 '07 #5

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.

Dec 10 '07 #6

AccessHunter

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

Dec 11 '07 #7

ADezii

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

You are quite welcome.

Dec 11 '07 #8