A "Count" Based off Filters set in a Pivot Table

So, we have imported data which we have queried and then created a pivot table off that query. It is essentially a list of files, each having unique ID numbers, and various attributes (file extension, type of document, hash, et开发者_如何学Cc). In any case, this data is based off "hits" on keyword searches from a different program. This means that there might be multiple records for the same Unique ID since there are multiple hits.

The pivot table allows us to illustrate/manipulate via filtering out certain criteria (e.g. we don't want certain file extensions or we don't want records with FIELD X or FIELD Y0. The report is fine, but we want to make a form/query/report/whatever that will pull a "count" (based off unique ID) which ignores duplicates. For example, once all the filters are set in the pivot table, based on the filters/output of the pivot table, we want something like this:

.PDF Files: 200 | total for field x | total field y | etc

.DOCX files: 320 | total for field x | total for field y | etc

Obviously, we want to ignore duplicates of the same Unique ID in the counts.

What is the best way to do this considering we will be manipulating the pivot table dynamically and often? The ideal scenario would to have the pivot table and another object (form/report/etc) open, and as the pivot table is manipulated whatever is displaying counts changes as well.

Here are some very rough notes notes. They are only minimally tested, and using IN would be a disaster with a lot of values, however, it would be easy enough to switch this round and use an excluded list. Perhaps you can get some ideas.

Dim oPTable ''PivotTable
Dim oPM   ''PivotMember
Dim oFUpd ''PivotFilterUpdate
Dim oChildren ''ChildMembers
Dim fset ''FieldSet

Dim sWhere As String
Dim sTemp As String
Dim sSQL As String
Dim sDelim As String
Dim aStates As Variant

Dim i As Integer

Dim rs As DAO.Recordset


sDelim = """"
aStates = Array("Cleared", "Checked")  ''Possible states

Set oPTable = Forms(0).PivotTable.ActiveView

sWhere = vbNullString

For Each fset In oPTable.FieldSets

    sTemp = vbNullString

    Set oChildren = oPTable.FieldSets(fset).Member.ChildMembers

    For i = 0 To oChildren.Count - 1
        Set oPM = oChildren(i)
        Set oFUpd = oPM.Field.FieldSet.CreateFilterUpdate

        If aStates(oFUpd.StateOf(oPM) - 1) = "Checked" Then
            Select Case fset.BoundField.DataType
                Case adChar, adLongVarWChar
                    sTemp = sTemp & "," & sDelim & oPM.Caption & sDelim
                Case adInteger
                    sTemp = sTemp & "," & oPM.Caption
                Case adDate
                    sTemp = sTemp & ",#" & oPM.Caption & "#"
                Case Else
                   '' The above is a very short list.
                   '' Stop

            End Select

        End If
    Next

    If sTemp > vbNullString Then
        sWhere = sWhere _
               & " AND [" & fset.Name & "] IN ( " & Mid(sTemp, 2) & ")"
    End If
Next

sSQL = "SELECT DISTINCT ID FROM [" & oPTable.Control.DataMemberCaption & "] "
sSQL = sSQL & "WHERE 1=1" & sWhere
Set rs = CurrentDb.OpenRecordset(sSQL)
MsgBox "Unique: " & rs.RecordCount

if that helps: http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

it will get you the unique count of ID numbers by numbers you want, and you can still manipulate the pivot