Problem with Count

[Leslie Isaacs]

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

 

[Duane Hookom]

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

 

[Leslie Isaacs]

Thanks Duane - your suggestion worked a treat!
Les

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

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