Help with a Report COUNT

[diazr]

I have a form called "HMLogMain" with the following fields [Account], [AccountName], [DateShred] and [ReasonToShred] that reside in the detail section. I also have a subform called "SubMenuShred" which captures the listing of the documents to be shredded with the following fields [DescOfDoc], [NoPages].

I wanted to run a report by date range so I created a query called "ShredListingQuery" and I joined "HMLogMain" and "SubMenuShred" using both tables using the field "ID" autonumber and named the query "ShredListingQuery".

The report points to the record source is "ShredListingQuery". I want to be able to get count for of each account with [ReasonToShred]. I do not want to count all the records in the subform. So for instance if I entered 6 documents for a particular account I want the count to be "1" and not "6".
I thought the function "DCount" would give the result but so far it gives me the same total as the function "Count". :banghead: Any help would be appreciated. Below I have the two examples I have tried thus far.

=Count(IIf([REASONTOSHRED]="Hold for more than 2 years",0))

=DCount("[Account]","ShredListingQuery","[REASONTOSHRED]= 'Hold for more than 2 years'")

 

[boblarson]

If you want a distinct count of the accounts in the table, then you need to only use that field in your query and use the Grouping to do it.

Select [Account], [AccountName], [ReasonToShred], Count([ReasonToShred]) As ReasonToShreadCount
From ShredListingQuery
GROUP BY [Account], [AccountName], [ReasonToShred], Count([ReasonToShread])

 

[diazr]

Thanks for the tip boblarson but I'm a newbie to MS Access and I'm not following your solution. I have attached a small scale of the db in hopes you can show me where to place your suggestion.

The report should count only once the account and give the number of "Accounts Closed" and "Hold for more than 2 Years" So in this db example the count total should be "2" "Accounts Closed" and "3" "Hold for more than 2 Years"

I appreciate your help.

 

[boblarson]

Okay, I think I have your report fixed. What I did is add a group footer for Account number with a text box in the footer with =1 and then set its RUNNING SUM property to OVER ALL. Then in the report footer I reference that text box in the control source of the text box which has the # of Accounts Closed.

By the way, in your sample I changed a few dates just so I could isolate things better.

See the revised attached.

 

[diazr]

Thank you so much for helping me I think I confused you in my explanantion of what I'm trying to achieve. I need the report to count the number of accurances for the reason for shredding. For example out of the five accounts listed in the report how many had "Account Closed" and "Hold for more than 2 Years".

 

[vbaInet]

Bob has already mentioned you that you need a query to return distinct records. In the attachment is a query called qryDistincCount which lists distinct records from your HMLogMain table using the Account field as the unique identifier. If you want the Account to be the unique identifier you can easily change the query.

I've used a DCount() function to demonstrate how you can get your get based on the results from qryDistinctCount for "Account Closed" only. Follow the same method for the other criteria. You can also use a subform for this too.

See attached.