Count of unique values in a report footer

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?

re: Count of unique values in a report footer

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" <nospam@ms.com> wrote in message
news:NyNdd.215$sA6.5496@nnrp1.ozemail.com.au...[color=blue]
> 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[/color]
reasons.[color=blue]
> Each record however is unique. What I would like to do is display the[/color]
total[color=blue]
> of the number of unique properties in the report footer, not just a count[/color]
of[color=blue]
> 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[/color]
determine[color=blue]
> the unique property count by using a recordset based on the query[/color]
underlying[color=blue]
> the report in the report footer on format event, but this would not work[/color]
as[color=blue]
> it complained of not having the required parameter which did not make[/color]
sense.[color=blue]
> 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[/color]
in[color=blue]
> the unique property count field in the report. Should it be an easy thing[/color]
to[color=blue]
> display a 'unique count' in a report footer?
>
>[/color]

re: Count of unique values in a report footer

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.svastits@cox.net> wrote in message
news:Yu%dd.6666$EZ.6012@okepread07...[color=blue]
> 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[/color]
identifier[color=blue]
> 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[/color]