Find Duplicates and Hides All But 1 Row In A Report

[matomo]

Hi all,

I have a problem and the problem is this: Is it possible to Find Duplicates and Hides All But 1 Row in Report?

In the attached database report 125 Evelyn Cres appears twice one record for the Public School and the other record for the Parking.

What I want to do is this Hide all the data in the second row (i.e. Parking, Other) related to 125 Evelyn Cres in exception of (keep) the following data fields Address, GFA Above Grade, GFA Below Grade Land Use Code and Land Use Description for the second row only.

I have tried the Hide property for each field but that only duplicate data even when the address is not the same.

Is it possible to achieve the above?

I have attached a copy the database.

matomo

 

[vbaInet]

10 Tables, 9 without Primary Keys, none of them linked. It's not possible in the state your db is currently in.

Unfortunately, you need to normalise.

 

[matomo]

VbInet,

only one table is needed in the report (i.e. tblROLLNUMBER).See the attached sample.

I have tried the code below but I am still missing something. What I am trying to do as explained before is to find duplicates if the street Address is the same and hide them when printing the report.

Code:

Private Sub cmdfinduplicates_Click()
' find duplicates and hides all but 1 row.
''''''''''''''''''''''''
Dim counter1 As Integer
Dim col1 As String ' column
Dim sell1 As String ' cell
Dim pap1 As String
Dim counter As Integer
Dim col As String ' column
Dim sell As String
Dim pap As String 'cell
Range("a1").Select
Sheet1.TextBox1.Text = "."
counter1 = 1
counter = 2
pap1 = "a1"
Do Until Sheet1.TextBox1.Text = ""
pause (0.25)
col1 = "a"
sell1 = counter1
pap1 = col1 + sell1
Sheet1.TextBox1.Text = Range(pap1)
counter1 = counter1 + 1
pause (".25")
''''''''''''''''''''''
col = "a"
sell = counter
pap = col + sell
Sheet1.TextBox2.Text = Range(pap)
counter = counter + 1
pause (".25")
If TextBox1.Text = "" Then
'must have got to end of field
MsgBox ("exiting sub")
Exit Sub
End If
If TextBox1.Text = TextBox2.Text Then
' hide duplicate rom
Rows(sell).Select
Selection.EntireRow.Hidden = True
MsgBox ("row test hidden")
Else
End If
Loop
MsgBox ("all done")
End Sub

Hope you can help.

Thanks in advance.

matomo

 

[vbaInet]

I had already looked at your database before making the comments in my last post. Your current structure wouldn't make this easy to achieve. The way Excel checks for duplicates is different from the way it's done in reports in Access.

 

[matomo]

VbaInet,

Thanks for your suggestion.

matomo