COUNT

[Guest]

I have a report based off of a query. There is one column that is 'Account
Number'. The report lists every item and if there is more than one item, the
account number is listed as well. I'm trying to do a count on the number of
account numbers, but don't want duplicates counted on the report footer
field. Things that I have tried so far are DCount, Count, and IIf. I'm not
sure of the formula syntaxt that should work. Also, I'm almost thinking that
this cannot be done in the report, but needs to originate in the query and
brought over to the report. Any suggestions?

 

[Marshall Barton]

I have a report based off of a query. There is one column that is 'Account
Number'. The report lists every item and if there is more than one item, the
account number is listed as well. I'm trying to do a count on the number of
account numbers, but don't want duplicates counted on the report footer
field. Things that I have tried so far are DCount, Count, and IIf. I'm not
sure of the formula syntaxt that should work. Also, I'm almost thinking that
this cannot be done in the report, but needs to originate in the query and
brought over to the report.

Use Sorting an Grouping to group on the account number
field. Select Yes for the group header. Move the account
number text box to the group header.

Add another text box (named txtRunCnt) to the group header
section. Set its control source to =1 and its RunningSum
property to Over All.

Then a report footer text box can display the number of
accounts by using the expression =txtRunCnt