By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,475 Members | 954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,475 IT Pros & Developers. It's quick & easy.

Display DISTINCT Count on Report Header

P: 77
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
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,669
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

P: 77
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
Expert 5K+
P: 8,669
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

P: 77
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
Expert 5K+
P: 8,669
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT qryCases.[Judge Name], Count(qryCases.[Case Nbr]) AS [Unique_Case_Count]
  2. FROM qryCases
  3. GROUP BY qryCases.[Judge Name];
Dec 10 '07 #6

P: 77
Thank you soooo much for helping me with the issues. You rock...
Thanks again.
Dec 11 '07 #7

ADezii
Expert 5K+
P: 8,669
Thank you soooo much for helping me with the issues. You rock...
Thanks again.
You are quite welcome.
Dec 11 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.