Search code examples
vbams-accessms-access-reports

Count row in ms access report that have same data in 2 column

  1. form before that user need to select and when search button is clicked, it will show a report based on user selection

  2. this is the report after the search button is click

Hi expert. I have a problem in looking for rows in ms access report that have duplicate data across columns family and name. So if in the first row column family = a and name = b, and in another row family = a and name = b, then we have a duplicate row regardless of other columns. I want it to count from the report not from the table or query. This is because the report will show based on user selection on combo box and list box from other form. and when the search button was clicked, then it will generate the report.

Therefore, I would like to have a button "Summary" in report where its can show result like below (based on report form):

the result

and so on ....

I hope i can get a positive feedback from you guys. Thanks

Below are the code that i used to generate the report :

Code for button report

Solution

  • one way to do this without passing more than 1 parameter is to place the summary in a sub report and reveal that sub report with the push of a button. Unfortunately sub reports in footers are buggy in access so you have to filter the sub report manually. we start with a similar simple normalized database: enter image description here Then I added a simple form with a multi-select listbox of families and a button to open a filtered report. enter image description here

    Private Sub cmdSearch_Click()
'Build Filter for report
Dim strFilter As String
Dim firstselectedfamily As Boolean
firstselectedfamily = True
For i = 0 To lstFamilys.ListCount - 1
If lstFamilys.Selected(i) = True Then
If firstselectedfamily = True Then
strFilter = "FamilyFK = " & lstFamilys.Column(0, i)
firstselectedfamily = False
Else
strFilter = strFilter & " OR FamilyFK = " & lstFamilys.Column(0, i)
End If
End If
Next i
'open report with filter
DoCmd.OpenReport "ExampleReport", acViewReport, "", strFilter, acNormal
End Sub

    here is the ExampleReport with a button to show a summary of duplicates: enter image description here

    The button reveals the hidden sub report based on a query that finds the duplicates: enter image description here

    The duplicates query is made by grouping based on family and test where both the count of familyID and TestID is at least 1: enter image description here Normally the summary report would be linked to the main report by a master child relationship, but the summary looks natural in the main reports footer where sub reports happen to be bugged and do not filter correctly. To get around the bug we provide code to filter the summary report:

    Private Sub cmdSummary_Click()
'filter summary form by using the main reports filter
Me.subfrmSummary.Report.Filter = Me.Filter
Me.subfrmSummary.Report.FilterOn = True
Me.subfrmSummary.Requery
'show/hide summary form
If Me.subfrmSummary.Report.Visible = False Then
Me.subfrmSummary.Report.Visible = True
Else
Me.subfrmSummary.Report.Visible = False
End If
End Sub

    Again, to get around the bug do not link the sub report to the main report with a master/child relationship. Instead set the filter with code.