Problem with Count

Hello All

I have a report based on a query that includes the two fields [payreference]
and [payamount].

There will normally be more than one record with the same [payreference].

The report does not need to display the individual records, only the total
of [payamount] for each value of [payreference], so I have a [payamount]
section footer containing the field =Sum([payamount]). Each value of
Sum([payamount]) is a payment to be made. The detail section of the report
is empty, and of zero height.

My problem is that the report also needs to display the total number of
payments to be made in the report footer, but if I put a field with
=Count(payreference]) I get the total number of records. e.g.

query output:
payreference payamount
abc £1234
abc £123
def £423
def £55
def £141
ghi £333

The count field in the report footer need to display the value 3, because 3
payments will be made (1 for each [payreference]), but Count(payreference])
returns a value of 6 (because of course there are 6 records underlying the 3
payments).

I cannot see how to get at the count of the number of payments, and would be
very grateful if someone could help.

Many thanks
Leslie Isaacs

Considering the information that you have provided, I would base the report
on a totals query that groups by PayReference and Sums PayAmount. Then place
the results in the detail section rather than a footer.

If you can't do this, then add a text box to the Group Header
Name: txtCountPayRef
Control Source: =1
Running Sum: Over All

The add a text box to the report footer
Control Source: =txtCountPayRef

--
Duane Hookom
MS Access MVP


"Leslie Isaacs" wrote in message
...
Hello All

I have a report based on a query that includes the two fields
[payreference]
and [payamount].

There will normally be more than one record with the same [payreference].

The report does not need to display the individual records, only the total
of [payamount] for each value of [payreference], so I have a [payamount]
section footer containing the field =Sum([payamount]). Each value of
Sum([payamount]) is a payment to be made. The detail section of the report
is empty, and of zero height.

My problem is that the report also needs to display the total number of
payments to be made in the report footer, but if I put a field with
=Count(payreference]) I get the total number of records. e.g.

query output:
payreference payamount
abc £1234
abc £123
def £423
def £55
def £141
ghi £333

The count field in the report footer need to display the value 3, because
3
payments will be made (1 for each [payreference]), but
Count(payreference])
returns a value of 6 (because of course there are 6 records underlying the
3
payments).

I cannot see how to get at the count of the number of payments, and would
be
very grateful if someone could help.

Many thanks
Leslie Isaacs

Thanks Duane - your suggestion worked a treat!
Les



"Duane Hookom" wrote in message
...
Considering the information that you have provided, I would base the
report
on a totals query that groups by PayReference and Sums PayAmount. Then
place
the results in the detail section rather than a footer.

If you can't do this, then add a text box to the Group Header
Name: txtCountPayRef
Control Source: =1
Running Sum: Over All

The add a text box to the report footer
Control Source: =txtCountPayRef

--
Duane Hookom
MS Access MVP


"Leslie Isaacs" wrote in message
...
Hello All

I have a report based on a query that includes the two fields
[payreference]
and [payamount].

There will normally be more than one record with the same
[payreference].

The report does not need to display the individual records, only the
total
of [payamount] for each value of [payreference], so I have a [payamount]
section footer containing the field =Sum([payamount]). Each value of
Sum([payamount]) is a payment to be made. The detail section of the
report
is empty, and of zero height.

My problem is that the report also needs to display the total number of
payments to be made in the report footer, but if I put a field with
=Count(payreference]) I get the total number of records. e.g.

query output:
payreference payamount
abc £1234
abc £123
def £423
def £55
def £141
ghi £333

The count field in the report footer need to display the value 3,
because
3
payments will be made (1 for each [payreference]), but
Count(payreference])
returns a value of 6 (because of course there are 6 records underlying
the
3
payments).

I cannot see how to get at the count of the number of payments, and
would
be
very grateful if someone could help.

Many thanks
Leslie Isaacs