|
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
| |
Share:
Expert 8TB |
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
- 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>;
- Place an Unbound Text Box in the Judge Name Header Section.
- Set its Control Source to:
- =DCount("*","qryCases","[Judge Name]='" & [Judge Name] & "'")
- I'm sure the SQL Gurus will come up with a better solution, but for now, this approach will work.
| | |
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
| | Expert 8TB |
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
- Create a New Report and set its Record Source = qryCases.
- Embed this Report as a Sub-Report into the Report's Footer.
- Do not attempt to Bind this Sub-Report to main Report, it is independent.
- Now, on the last page of the Report, (Report Footer), the Sub-Report will appear.
| | |
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.
| | Expert 8TB |
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];
| | |
Thank you soooo much for helping me with the issues. You rock...
Thanks again.
| | Expert 8TB |
Thank you soooo much for helping me with the issues. You rock...
Thanks again.
You are quite welcome.
| | Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
reply
views
Thread by Yasin |
last post: by
|
1 post
views
Thread by Steve Heath |
last post: by
|
1 post
views
Thread by seeker52 |
last post: by
|
4 posts
views
Thread by dixie |
last post: by
|
17 posts
views
Thread by keith |
last post: by
|
1 post
views
Thread by nfrodsham |
last post: by
|
1 post
views
Thread by Beowulf |
last post: by
|
1 post
views
Thread by Dave |
last post: by
| | | | | | | | | | | |