Complex Report/Query Help

[gregch]

How do I get a count of firms by location when I am trying to get a total of $ paid in the same report? These records could contain multiple $ amounts for each firm. I only want each firm counted once. I have created a main report that is grouped by location. There is a subreport that will count the number of firms by location correctly, but I need to get a grand total of firms on the main report.

I have a list selection that a user can select multiple firm locations when opening the main report - this may create some problems in finding an answer.

Any help would be greatly appreciated!
Greg

 

[vbaInet]

Here are two ways:

1. Perform the count in the Format event of the Detail section by storing the CompanyIDs in a dictionary object and using the dictionary's Exists method before adding. The number of items in the dictionary (or collection) will be your count.

Or

2. Create a query that will select and count distinct companies based on your report's record source:

Dim strSQL as string

strSQL = "SELECT DISTINCT Count(Q.CompanyID) As CompaniesCount " & _
             "FROM (" & [COLOR=Blue][B]left(Me.RecordSource, Len(Me.RecordSource) - 1)[/B][/COLOR]  AS Q & ";"

The code in blue is obviously your report's record source but we needed to do a bit more to get rid of the semi colon. The code will go in your report's On Load event.

That would then be the record source of another subreport located at your main report's footer section. Create a textbox in that subreport and set it's control source to CompaniesCount.

If you're having troubles with this method the just run it in the report's footer section's Detail event and use a DCount (will need to adjust the sqlString) or a DLookup with that sqlString.

 

[gregch]

Thanks for the help! I knew that there should be a way to get this count.

Greg

 

[vbaInet]

No problemo!