Total number of groups

  • Thread starter Thread starter Neek
  • Start date Start date
N

Neek

Hello everyone,

I was hoping someone could help me with the logic for this
report I am working on.

Currently, the detail section looks kinda like this (and
many more fields yet):

Part # | Section | Contractor | Contract Type |


What my client wants to do for the footer of this report
is total the # of kinds of parts listed above, # of kinds
of sections, # of Contractors, # of contract Type, etc,
etc, etc...

Part # | Section | Contractor | Contract Type |
4 | A | Good Stuff | 2 |
3 | A | Good Golly | 2 |
4 | A | Good Stuff | 1 |
4 | B | Good Golly | 1 |
3 | B | Good Stuff | 1 |
2 | B | Good Stuff | 1 |
4 | A | The Best | 3 |

would be:
Total Parts: 3
Total Sections: 2
Number of Contractors: 3
Contract Types: 3


This is not a simple total or count of the fields above
the list because there could be numerous lines with the
same part # or contractor for instance and I dont want to
count duplicate values.

I have tried grouping the report by the fields they want
to group, but this skews the data to display incorrectly
because there are so many fields to group individually.

I also do not want to mess with subreports/subqueries and
such because I want the user to be able to filter this
report on the fly.

Thanks so much,

Neek
 
I've done a similiar thing in the past...
Here's the code for the function I used... It tries to emulate the built in
domain aggregate functions.

Public Function DUniqueCount(Expr As String, Domain As String, Optional
Criteria As String) As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

'construct SQL
SQL = "SELECT DISTINCT " & Expr
SQL = SQL & " FROM " & Domain
If Criteria <> "" Then
SQL = SQL & " WHERE " & Criteria
End If

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

'need to move to last record for RecordCount to be correct
rs.MoveLast
DUniqueCount = rs.RecordCount

rs.Close
db.Close

End Function

Paste this code into a module, then set the recordsource for each total to
something like...
=DUniqueCount("Part#","tblYourTableName")

HTH
Sam
 
Back
Top