Conditionally remove rows from ms access query (SQL)

I would like to remove rows (about 10000) from a report in ms access. I am comparing two sets of two column in a table. When I run the query I would like it to remove the rows where column 1 and 3 match and column 2 and 4 match. I have tried running a left join query and trying with vba code but can’t get it to work.

Here is the data

I have tried using VBA

Private Sub Report_Open()
   Dim prism_box As String
   Dim prism_recs As String
   Dim keepdrop_box As String
   Dim keepdrop_recs As String

   prism_box = CStr(Me.[tbl_KeepDrop_remainingpackets_RecId])
   prism_recs = CStr(Me.[tbl_KeepDrop_remainingpackets_RecId])
   keepdrop_box = CStr(Me.[Duplicate Recids_Box#])
   keepdrop_recs = CStr(Me.[Duplicate Recids_RecId])

   If prism_box = keepdrop_box & prism_recs = keepdrop_recs Then

   End If
End Sub

I have tried using query too but won’t delete them.

I would like the final result to report a table like this…

Answer

Since this is a report, you are better off creating a query that excludes what you don’t want to see.

Pull in whatever fields you need then create two more fields that have iif([column1] = [column3], True, False) and iif([column2] = [column4], True, False Add into criteria False and then point your report’s recordsource at that query. (Change the column1-4 into their actual names)

Advertisement

Answer