Exclude duplicate records when counting in a report

[Fuzz_cozens]

Hi

I am using Access 2010 on win 8

I have a basic report and in the group footer I would like to count the records for the filed called [policy number]. As this field returns duplicate records I would only like each specific policy number to be counted once in my total.

Please can someone help me as when I use in the control source =count([policy number]) then every single record is counted and I only want the unique ones counted.

I would really appreciate any help with this please

Richard

[JoeM]

One way is to create an Aggregate Query which weeds out the duplicate.
Then, you can count the records in that Aggregate Query from your Report either by using the DCOUNT function, or creating a Subreport from the Aggregate Query, and placing that Subreport on your Main Report.

There may be other ways too. Those are just some options that came to my mind.

[Xipooo]

You could also do it in code behind the report.

Add a group to your report based on policy number. Click on the "Policy Number" header bar so that it's highlighted. Set the Height to 0. In the "On Format" event create a new sub routine.

Under where it says "Option Compare Database" at the top, type:

Dim intTotal as Integer

Then in the Sub created for the Header Format add this:

intTotal = intTotal + 1
Me.<textbox> = intTotal

Replace <textbox> with whatever the name of the textbox is on your form that is supposed to show the total number of policy numbers.


JoeM's option is also quite good and requires no VBA code. Just a totals query based on the saved query you have for the report. Is the query you're using to make the report a saved query? If so, what is it named?

[Fuzz_cozens]

Thanks Xipoo for your detailed feedback.

Yes the query is a saved query and it is called "Commission Statement - 1Life Agent Summary"

The data I receive contains many records for commission purposes listed per policy number therefore 1 policy number will have a few records with different commission values. I have grouped the report per policy number as I don't want every single line reflected on the report but I need the total commission value per policy number which I have accomplished.

I also have a "Commission Type" group which is above the Policy number so therefore if the commission type is "First Year Commission" then all policy number for that commission type will be grouped. I then need a total per commission type to reflect in the commission type footer counting the number of policy numbers excluding duplicated ones.

I hope this makes sense pal

[JoeM]

So, why not do an Aggregate Query where you group the records at the query level and return the subtotals you are looking for, and then use that Aggregate Query as the basis of your Report?

[orange]

Just curious, but why are there duplicates -especially Policy Numbers?

[Xipooo]

So, why not do an Aggregate Query where you group the records at the query level and return the subtotals you are looking for, and then use that Aggregate Query as the basis of your Report?

I'm thinking he may want ALL results to show, but just have a totals field which excludes duplicates. If he based the report off of the aggregate query, he'd lose his duplicates on the report. Not a very common request, but it is sometimes necessary.

[Xipooo]

Thanks Xipoo for your detailed feedback.

Yes the query is a saved query and it is called "Commission Statement - 1Life Agent Summary"

The data I receive contains many records for commission purposes listed per policy number therefore 1 policy number will have a few records with different commission values. I have grouped the report per policy number as I don't want every single line reflected on the report but I need the total commission value per policy number which I have accomplished.

I also have a "Commission Type" group which is above the Policy number so therefore if the commission type is "First Year Commission" then all policy number for that commission type will be grouped. I then need a total per commission type to reflect in the commission type footer counting the number of policy numbers excluding duplicated ones.

I hope this makes sense pal

I take back what I said to JoeM then. You really DO want to make your query an aggregate query and base your report off of that instead of one which displays all of your results.

[Fuzz_cozens]

Thanks for the feedback. Will give that a try and let you know