Finding duplicate records using VBA


Hi All,

I am new to MS Access VBA, but not to VBA or to MS Access.

Thanks in advance for whatever help you can give me.

I am trying to verify whether or not duplicate records exist in a table (General Flying & Ownership Expenses) using VBA. What is the best way to proceed? I designed a query that counts duplicate records (see below). A duplicate record would be two records having the same "Type" and the same "Year"


SELECT

[General Flying & Ownership Expenses].Year,
[General Flying & Ownership Expenses].Type,
Count([General Flying & Ownership Expenses].Type)

AS CountOfType

FROM [General Flying & Ownership Expenses]

GROUP BY

[General Flying & Ownership Expenses].Year,
[General Flying & Ownership Expenses].Type

HAVING

(((Count([General Flying & Ownership Expenses].Type))>1));



What I want is if the results of this query for CountOfType are greater than 1 (i.e. there are duplicates), a message box will pop up indicating the table where the error is coming from and for the Sub to halt (see pseudo-code below).



Pseudo-Code

Sub Duplicate_Tester

Duplicate Test Process
if (results from Duplicate Test Process) > 1 then
msgbox(“Duplicate records in General Flying and Ownership Expenses”)
End
end if

End Sub



How to do this? Should I be using a query at all? I tried using the MS Access help and found it baffling.

Thank you!

- Kevin


Sponsored Links:



Hi all,

I have inherited a database with a table with plenty of duplicate records. In some cases the a record may appear more than once.

I have kindly been given the following code but somehow I cannot get it to work. I am getting a runcode error.

In my example database I have three fields a, b and c
and I have duplicate records:-


Please note I am using A2003....I have attached word doc with code for anyone's perusal.

Just amended my post to include my sample database. I am also providing, again, some background to my problem:-

I've inherited this database with a table which has accidentally been populated with records that already exist.

I have set up a sample database that more or less mimics the real one. I have omitted PKs and other database rules because that is what I have. I "simply" require a coherent piece of VBA code that will save me the trouble of sorting the records and then deleting the duplicate records that I see.

Obviously I would be working on a copy to start of with so that I would first understand the code before I embark on the real thing.

I completely understand the concerns that maybe the database is structured improperly or whether additional fields have to be included, but my main concern is deleting the duplicates.

The code I am supplying has been kindly offered to me but is not working. I believe its almost there, and I am sure it just requires some tweaking.

Basically what I need is code which will sort the table and then compare the entire contents of one record with the one below it. If it is the same, ie. all the fields are the same, then one of the duplicates should be deleted.

Thanks and any advice on the code grately appreciated.




Hi! Anyone here knows how to filter a table record using VBA?

For example, I have a list of customers. I want to filter them to those living in the East only. How do i do it using VBA code?

Please reply.. Really need help here. Thanks!




Hello,
Is it possible to do a find and replace using VBA or SQL.
I would like to be able to replace fields (the attendee's name) in my attendance_table.

Each attendee's name appears multiple times in the attendance_table, so I am thinking that a loop would be the answer but I do not how to do a loop.

For example:

If a person changes their name (by marriage for example), I would like to be able to find all instances where the previous name appears in the attendance table and replace it with their new married name.

I hope this is possible.

Thank you for your time.




IS there a way to find Duplicate Records from two different tables? The Duplicate Query Wizard only allows for one table. I have a talbe of "Users" and a table of "Users who got new Laptops" I want to compare the two tables and find duplicate records so that I can find out if anyone on my "User" list has received a new Laptop.

Thank You,
Kendra




What is the best way to find duplicate records in a table with 25 columns!?

I know there is a built in find duplicate query but it only allows a max of 10 columns to be compared.

Edit: I actually want to find duplicates records between two tables both the same layout and columns

Any help welcome
Kind regards,

Peter


p.s.
Sorry if this thread gets posted twice, there was an error when I first tried to post it.




Hi,

Is there some way I can select a record using VBA, as if I clicked on the record selector at the left side of a form? (the record selector is marked black)

TIA




I want to find duplicate records of one field in a table and delete all but one of the records using VBA.

I was thinking of using: CurrentDb.Execute "DELETE *
but I'm not sure how to put what I want after that.

I did use the Query Wizard and was able to get the duplicate SQL needed:

SELECT First(tblUserID.[UserID]) AS [UserID Field], Count(tblUserID.[UserID]) AS NumberOfDups
FROM tblUserID
GROUP BY tblUserID.[UserID]
HAVING (((Count(tblUserID.[UserID]))>1));

Any help would be much appreciated! Thanks.




I would like to remove all duplicate records from a table I have setup. I have created a find duplicates query using the wizard which works great. What would I put in for a statement to add to the query that would delete the duplicates. It doesn't matter which record gets deleted as long as there's no duplicate. Here is my setup:

My table name is "BCES" and the field I will be using to remove duplicates from is called "BC"

Thanks!




Hi, I have a table contains 5 fields are shown as below, how I can write a query to find out the duplicate of the records? I have created a couple of queries by using Find Duplicates Query Wizard to do it. But the results are not accurate enough.

Field 1: ID
Field 2: Trading Name
Field 3: Location No
Field 4: Location Street
Field 5: Location Suburb

ID Trading Name Location No Location Street Location Suburb
41350 Arabica Café Grey St
263 Arabica Cafe` 1 GREY STREET
41294 Arashi Japanese Resturant 41 Courtenay PL
150 Arashi Kushi Yaki Bar 41 COURTENARY PLACE



From the data sample, I could like to find out the duplicated records. One of the queries is shown as below. But they cannot pick up these records. Any better way to find the duplicated records? Please help.

SELECT DISTINCTROW [C&B-will].[trading name], [C&B-will].[location street], [C&B-will].Field1, [C&B-will].[location no]
FROM [C&B-will]
WHERE ((([C&B-will].[trading name]) In (SELECT [trading name] FROM [C&B-will] As Tmp GROUP BY [trading name],[location street] HAVING Count(*)>1 And [location street] = [C&B-will].[location street])))
ORDER BY [C&B-will].[trading name], [C&B-will].[location street];




Hi there,

I used Access quite a lot a few years ago, but as I no longer use it I am now very rusty, so I have a question which I hope somebody can help with!

Basically I have a table with one field called 'email'. I'd like to create a query which finds duplicate records (there could be two records, or there may be more) and then deletes both/all of them, so that there is no trace of that email address left anywhere in the table at the end.

I would be very grateful if somebody could help me out with this, and meanwhile send thanks for your time.




Hi,

I have imported an excel table into access succcefully. The records contain the following fields: NIIN, FSC, SD, NET QTY, NRTS QTY, Credits, Net, and Noun. Each record has a very unique NIIN. I have written a query to find all duplicate NIIN records and sum up fields NET QTY, NRTS QTY, Credits, Net respectively then append the summed up records to a different table. The query works fine at first.

After analyzing the results, I noticed that there are some NIIN records in which all the data is a duplicate except for the Noun field. Based on the NIIN, the Noun fields should be identical, but due to typos when entered into excel (data received from external source), they are not and thus those records arenot considered duplicates. In reality they are duplicate records.

Is there a way, by using a query, to locate duplicate records by the NIIN field (but not include the Noun field), sum up fields NET QTY, NRTS QTY, Credits, Net and append the summed up record to a new table that does includes the Noun field?




I have a continuous form, and I would like to programatically find and select one of the records with VBA if it meets certain conditions. How would I do this? Basically trying to control the record selector arrow with VBA.




If the form I am using already has a record using a specific number, I want the user to be aware before they enter all the info. I found this code and it works except when it redirects me to the existing record, it doesn't. It redirects me to record #1. The field booking number is not a PK or AutoNumber field. It is a number field and as you can imagine, each is very specific to each person. Using that number as the PK field just won't work for this application. What I would like to accomplish is to be redirected to an existing booking number if it exists, otherwise the user will just fill in the form as needed. Here's what I have"

CODE:
[Private Sub Booking_Number_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.BookingNumber.Value
stLinkCriteria = [BookingNumber] = " & " '" & SID & "'"
'Check tblClient table for duplicate BookingNumber
If DCount("*", "tblClientInfo", "[BookingNumber]=" & Me.BookingNumber) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Booking Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Booking Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub]

Any help will be greatly appreciated




What is the recommended method for deleting records using a VBA module? Currently I am using an SQL method that specifies the table name and the upper time stamp date from which values should be deleted.

My database is continually being updated with new files from this same VBA module. I would like to delete old records based on a rolling window (say 30 days). Would this be the best way to go? It seems a bit slow...but then again my database contains 1 million + records.

Thanks.




With my form open, I want a button that when clicked will create a new record using VBA and save it in my table. Then I want it to GoTo the record (or last record in the table) in the current form.

The following code does all of the above, EXCEPT it goes to the last record of the form, rather than the last record of the table.

Here's my code.

Dim dbTransportationPlanning As DAO.Database
Dim rstCurrent_Shipments As DAO.Recordset
Dim Shipment_num As String
Set dbTransportationPlanning = CurrentDb
Set rstCurrent_Shipments = dbTransportationPlanning.OpenRecordset("Current_Sh ipments")
rstCurrent_Shipments.AddNew
rstCurrent_Shipments("Product").Value = "ONE"
rstCurrent_Shipments("TNProject").Value = "NEW"
rstCurrent_Shipments("Shipment_Num").Value = "NEW"
rstCurrent_Shipments("Departure_Date").Value = "1/1/1900"
rstCurrent_Shipments("Shipper").Value = "NEW."
rstCurrent_Shipments("Consignee").Value = "NEW"
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acLast
DoCmd.OpenQuery ("Sharepoint_Append")
DoCmd.SetWarnings True




Hi all,
I have this database to keep track of our company's licenses. It works fine. We have about 250 records, each record contains licensee's info and also has an ole field linked to a folder that gets updated directly from the form.

All the linked files are pdf files.

Whenever we have a new license or one that needs to get updated we do (right click ----Insert Object ---etc).

The problem I am having is..

If the user is updating a license or replacing an expired one with a new one, the user needs to access the folder, move the expired License to a new folder and name the new license so it gets linked to the database.

Is there a way to do this process using vba like a button to update or replace the existing pdf file.

the linked files are under a folder like this:

R:operLicensesFolder(usualy a state name)Filename.pdf

Thanks in advance




I am creating a small Access application that will allow me to update my Call handling system's site table.
It uses an mdb file, and I wish to add new sites (from new contracts) to the SCSite table.

I recieve regular updates of new sites from existing customers, so want to create a simple macro that will

1: import (from an excel file) the new sites and add these to the existing SCSite table
2: Check for duplicate records using the Site_Num primary key
3: Merge non duplicate records into existing SCSite table.


Is this possible, and if so - how? VBA? Macro builder? Query?


A Swift response would be great, I have had a look through previous posts - but to no avail!




I have a table on which I cannot set a primary key because there are duplicate records in the table. How can I use a query to show the records that are duplicated?

Robert




Need help and know no to very little code.

Have a table DONORS that has assigned Soundex for finding possible duplicate donors. The fields I need to compare are Soundex,Lname.Fname,DOB,DonorId so that when I create a report from the query I can group possible duplicates in pairs to be researched to determine if they are actual duplicates are not.
I tried using the find duplicate records wizard to no avail.

I think I should be looking at using an Alias for the Donors table when the designing query. Need something like If Soundex and Lname and or DOB are same then pair these two where DonorId is not =.

Will be willing to e-mail table to anyone that might be able to help.

Thanks

RC