Compare 2 tables and output a new table

[sandingdude]

I have an access database with 2 tables within it. I need to write a VB script that will compare the data in 2 tables (which each have the same exact fields). Within the 2 tables I need it to compare the FNAME and LNAME from each table and prompt me with a dialog box displaying the FNAME, LNAME, PHONE from each table that is a duplicate asking me to either ignore or remove from Table 1. Table 1 is actually an import of data and Table 2 is the main table. Table 1 is actually a purchased list of mailing addresses. After I compare it to the present database and make sure there are no duplicates. I will take Table 1 and import it into our main database. Any ideas on how to do this? Thank you for your help.

Examples below:

The Tables

Table 1
FNAME LNAME PHONE
Bob Smith 577-2323
Dan Brown 232-5234

Table 2
FNAME LNAME PHONE
Bob Smith 555-2323
Robert Green 232-3234

The Dialog Box

A Duplicate Was Found

Table 1
Bob Smith 577-2323

Table 2
Bob Smith 555-2323

Would you like to ignore or remove entry from Table 1?

 

[flyover789]

the following code should do what you want:

Function import()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
[COLOR=green]'SQL string for names that already exist in Table2[/color]
strsql = "SELECT Table1.FNAME AS FNAME1, Table1.LNAME AS LNAME1, " & _
"Table1.PHONE AS PHONE1, Table2.FNAME AS FNAME2, Table2.LNAME AS LNAME2, " & _
"Table2.PHONE AS PHONE2 " & _
"FROM Table1 INNER JOIN Table2 ON (Table1.LNAME = Table2.LNAME) AND " & _
"(Table1.FNAME = Table2.FNAME);"

Set db = Application.CurrentDb
Set rs = db.OpenRecordset(strsql)
rs.MoveLast
rs.MoveFirst

Do Until rs.EOF

If MsgBox("Name already exists:" & vbCrLf & vbCrLf & _
"Table 1" & vbCrLf & _
rs.Fields("FNAME1").Value & " " & rs.Fields("LNAME1").Value & " " & _
rs.Fields("PHONE1").Value & vbCrLf & vbCrLf & _
"Table 2" & vbCrLf & _
rs.Fields("FNAME2").Value & " " & rs.Fields("LNAME2").Value & " " & _
rs.Fields("PHONE2").Value & vbCrLf & vbCrLf & _
"Do you want to delete the record in Table1?", vbYesNo + vbQuestion, _
"Duplicate found") = vbYes Then

[COLOR=green]'Delete the duplicate record:[/color]
DoCmd.RunSQL "DELETE * FROM Table1 WHERE Table1.FNAME = '" & _
rs.Fields("FNAME1").Value & "' AND Table1.LNAME = '" & _
rs.Fields("LNAME1").Value & "' AND Table1.PHONE = '" & _
rs.Fields("PHONE1").Value & "';"

rs.MoveNext

Else:
rs.MoveNext

End If

Loop


End Function

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

 

[sandingdude]

Thanks flyover. I'm going to play around with it now.

 

[sandingdude]

flyover

I was wondering if you could point me in the right direction of adding this code into my access database? I am new to this and was hoping you could help me out.

Thank you in advance.

 

[flyover789]

Hello sandingdude,

create a new module and paste the a.m. code in it. Save the module and name it just as you wish.

Add a button to a form and in the On Click field of the events tab of the properties window of the button, enter: =import()

Now a klick on the button will invoke the function.

Please note, that the function is designed for the table structure (Table1, Table2) with the fieldnames FNAME, LNAME, PHONE as you described in your post. Let me know if you need help in adapting it to your real db.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database Systems and Applications across all Business Areas[/blue]

 

[sandingdude]

Thank you so much! It works great.

Sandingdude

 

[sandingdude]

Hey fly

Thanks for the help last month. I was wondering if the following is possible. If you recall myself comparing FNAME and LNAME in 2 tables. So if there is a Bob Smith in each table it will prompt me that there is a duplicate. How hard is it to compare the first character of the FNAME in two lists. This is important because I found that there is actually just initials in the FNAME that are actually duplicates.

Example

Table 1
FNAME LNAME
John Smith
Dan Brown
Jimmy Dean

Table
FNAME LNAME
J Smith
Dan Brown
J Dean

I need it to prompt me with

John Smith and J Smith

Can you help me with this?

Thanks,

Dave

 

[PHV]

Replacre this:
"FROM Table1 INNER JOIN Table2 ON (Table1.LNAME = Table2.LNAME) AND " & _
"(Table1.FNAME = Table2.FNAME);"
By this:
"FROM Table1 INNER JOIN Table2 ON (Table1.LNAME = Table2.LNAME) WHERE " & _
"(Table1.FNAME = Table2.FNAME) OR (Table1.FNAME = Left(Table2.FNAME,1)) OR (Left(Table1.FNAME,1) = Table2.FNAME);"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sandingdude]

I was wondering how I can display a message box that says "No Duplicates Found" just in case there are no duplicates?

Thanks

 

[sandingdude]

I'm working on this database a little more and I was noticing that when everyone was first helping me we were looking at FNAME, LNAME, and PHONE. I need to add COMPANY as a field name to compare both tables. Can you guys help me fix the code for this.

Thanks,

Dave