Problem with Count

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

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
 
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
 
Thanks Duane - your suggestion worked a treat!
Les



Duane Hookom said:
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 said:
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,
Click to expand...
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
Click to expand...
the
3
payments).

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

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
Back
Top