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

Count of unique values in a report footer

P: n/a
SJM
I have a report that displays records of real estate properties. It is
possible for each property to appear a number of times for various reasons.
Each record however is unique. What I would like to do is display the total
of the number of unique properties in the report footer, not just a count of
the number of records. I have experimented with grouping on the property
field and using running sums but to no avail. I have also tried to determine
the unique property count by using a recordset based on the query underlying
the report in the report footer on format event, but this would not work as
it complained of not having the required parameter which did not make sense.
Maybe I could save the underlying data to a table and base the report on
that and then get a recordcount on a recordset based on the table to fill in
the unique property count field in the report. Should it be an easy thing to
display a 'unique count' in a report footer?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ed
SJM:

Try this out for size. It assumes that you have a table or a query as the
record source for the report and that [PropertyID] is the unique identifier
of the property and that [PropertyID] is an output column of the record
source. Create a field named txtUniqueRecs in the report footer section,
then copy this code into the On Print event.

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
On Error Resume Next
Dim rst As DAO.Recordset
Dim strRecSource As String
Dim strSQL As String
strRecSource = Me.RecordSource
strSQL = "SELECT DISTINCT PropertyID FROM (" & strRecSource & "); "
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.BOF And rst.EOF Then
'there's no data,
'return 0
Me!txtUniqueRecs = 0
Else
rst.MoveLast
Me!txtUniqueRecs = rst.RecordCount
End If
Set rst = Nothing
End Sub

Add whatever error routine you normally use.

Good luck,
Ed

"SJM" <no****@ms.com> wrote in message
news:Ny****************@nnrp1.ozemail.com.au...
I have a report that displays records of real estate properties. It is
possible for each property to appear a number of times for various reasons. Each record however is unique. What I would like to do is display the total of the number of unique properties in the report footer, not just a count of the number of records. I have experimented with grouping on the property
field and using running sums but to no avail. I have also tried to determine the unique property count by using a recordset based on the query underlying the report in the report footer on format event, but this would not work as it complained of not having the required parameter which did not make sense. Maybe I could save the underlying data to a table and base the report on
that and then get a recordcount on a recordset based on the table to fill in the unique property count field in the report. Should it be an easy thing to display a 'unique count' in a report footer?

Nov 13 '05 #2

P: n/a
SJM
Ed.
This is very similar to what I tried except...
A. I used ADODB instead of DAO.
B. I referenced the underlying report query rather than the report
recordsource.
I was wanting to do something based on RecordSetClone but this is not
available in reports, but using the report's recordsource in the sql like
you outline below could be the angle I am looking for. I haven't tried it
yet, but thanks anyway, I will give it a go.

"Ed" <ed*********@cox.net> wrote in message
news:Yu%dd.6666$EZ.6012@okepread07...
SJM:

Try this out for size. It assumes that you have a table or a query as the
record source for the report and that [PropertyID] is the unique identifier of the property and that [PropertyID] is an output column of the record
source. Create a field named txtUniqueRecs in the report footer section,
then copy this code into the On Print event.

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
On Error Resume Next
Dim rst As DAO.Recordset
Dim strRecSource As String
Dim strSQL As String
strRecSource = Me.RecordSource
strSQL = "SELECT DISTINCT PropertyID FROM (" & strRecSource & "); "
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.BOF And rst.EOF Then
'there's no data,
'return 0
Me!txtUniqueRecs = 0
Else
rst.MoveLast
Me!txtUniqueRecs = rst.RecordCount
End If
Set rst = Nothing
End Sub

Add whatever error routine you normally use.

Good luck,
Ed


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.