Want to Compare tables to find Duplicates 3

[Holmsy]

Hello:
I am trying to create a method of comparing parts list by comparing spare parts lists for new equipment
to a list of all current parts. The list that I get from the new equipment maker contains a spec #, Description ,Manufacturer and other data
I want to compare the spec number to my current list and display any that are already set up into a new table and all that are new into
another table.

 

[fneily]

Call new list Table1, current list Table2. Set up a query Table1 to Table2. In the query grid, the first column will have spec# from Table1, and the second column will have spec# from Table2. Manually Link the tables by spec#. Double click on the link line and select Option 2, Left Outer Join. This will produce an answer showing all new spec#'s and ONLY the matching current spec#'s. You can then go back to Design view and place a condition on the current spec# column testing for Is Null. This will give you all the new spec#'s with no matching current spec#'s. You can then change this query into a Make Table query so you now have a table with just the new spec#'s that are actually new.
You can then do the same thing again, but this time change the criteria to Is Not Null to get a listing of new spec#'s with matching current spec#'s. Then again do a Make Table query.

 

[Holmsy]

That works good , but can you tell me how to add variables
to only search for a part of the spec rather than the whole
spec. Some times the spec that is in the data base is not
complete or has more numbers or letters.

 

[SBendBuckeye]

Hello Holmsy,

I think subselects should do the trick for you. Assume you have 2 tables, tblNew and tblMaster. Try something like the following (SUI stands for SomethingUniquelyIdentifying):

SELECT * FROM tblNew
WHERE tblNew.SUI NOT IN
(SELECT tlbMaster.SUI FROM tblMaster);

Then do the same thing with IN instead of NOT IN and the two record counts should equal all of the records in tblNew.

Some examples of SUIs:

tblNew.SpecNo NOT IN etc Left$(tblMaster.SpecNo, 4)
(tblNew.SpecNo + tblNew.PartNo) NOT IN etc (tblMaster.SpecNo + tblMaster.Item)

Feel free to post again if this gets you started and you need to refine the process. It would be helpful if you posted some examples of the types of partial matches you are encountering.

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[Holmsy]

Thanks for the reply: I am not sure how to apply your
suggestion. I have attached the VB code that I am using
now in conjuction with the make table macros suggested
earlier.Can you review this and offer any suggestions
to use other variables in the comparison.

Option Compare Database
Function DoComparison()
FindMatchingSpecs
End Function
Sub FindMatchingSpecs()
Dim dbobject As Database
Dim NewPartInfoRs As Recordset
Dim StoresInfoRS As Recordset
Dim MatchPartsRS As Recordset
Dim NewTechSpec As String
Dim StoresTechSpec As String
Dim HoldNewPartSpec As String
Dim HoldMatchingPartSpec As String
Dim HoldMaterial As String
Dim HoldMFG As String
Dim strLeftAmount As String
Dim intLeftAmount As Integer
Dim HoldLeftStores As String
Dim HoldLeftNew As String
Dim strQuery As String
Dim strQuery2 As String
Dim strQuery3 As String
Dim strInsert As String
Dim msgboxresult As Long
Dim Message As String
Dim Title As String
Dim Default As Integer
Dim HoldCode As String
Dim SupplierCodeRs As Recordset
Dim Checknumber As String
Dim Starttime As String
Dim Endtime As String
Dim TotalTime As String

On Error GoTo DensErr_Handler
Set dbobject = CurrentDb




Message = "Enter the number of characters you would like to use in your comparison. Note** Comparison starts at left of the Field! A smaller number gives More Results but Less Accuracy" ' Set prompt.
Title = "Match String Input" ' Set title.
Default = "4" ' Set default.
' Display message, title, and default value.
strLeftAmount = InputBox(Message, Title, Default)

intLeftAmount = CInt(strLeftAmount)

strQuery = "SELECT * FROM TBLNewParts_NoMatch;"
strQuery2 = "SELECT * FROM tblallparts;"
strQuery3 = "SELECT * FROM MatchingParts;"

Set NewPartInfoRs = dbobject.OpenRecordset(strQuery)
Set StoresInfoRS = dbobject.OpenRecordset(strQuery2)
Set MatchPartsRS = dbobject.OpenRecordset(strQuery3)

With MatchPartsRS
.MoveFirst
Do While Not .EOF
.Delete
.MoveNext
Loop
End With



With NewPartInfoRs

.MoveFirst

Do While Not .EOF
'[Forms]![Switchboard]![txtCheckingPart] = .AbsolutePosition
Checknumber = "Checking Part Number---->>> " & Str(.AbsolutePosition)

DoCmd.Echo True, Checknumber


NewTechSpec = ""
NewTechSpec = .Fields("newparts_Technical Spec&quot.Value
HoldLeftNew = Left(NewTechSpec, intLeftAmount)
'MsgBox NewTechSpec
With StoresInfoRS
.MoveFirst

Do While Not .EOF
StoresTechSpec = .Fields("Technical Spec&quot.Value
HoldLeftStores = Left(StoresTechSpec, intLeftAmount)
'MsgBox (Left(.Fields("Technical Spec&quot.Value, intLeftAmount))
'MsgBox (Left(NewTechSpec, intLeftAmount))
'Debug.Print HoldLeftStores, HoldLeftNew
If HoldLeftStores = HoldLeftNew Then

HoldNewPartSpec = NewTechSpec
HoldMatchingPartSpec = .Fields("Technical Spec&quot.Value
HoldMaterial = .Fields("Material&quot.Value
HoldMFG = .Fields("MFG&quot.Value
With MatchPartsRS
.AddNew
.Fields("NewPartSpec&quot.Value = HoldNewPartSpec
.Fields("MatchingSpec&quot.Value = HoldMatchingPartSpec
.Fields("Material&quot.Value = HoldMaterial
.Fields("MFG&quot.Value = HoldMFG
.Update
End With

End If
.MoveNext
Loop
End With
.MoveNext
Loop

End With

Exit Sub

 

[SBendBuckeye]

Can you give some descriptions of what your are trying to do? As an example, Vendor part number = first six bytes of item number, etc. Some examples would also be helpful. Are you trying to match on multiple fields? If so, is there a hierarchy (eg if match1 then done, else if match2 then done, else match3, etc). Depending on you are trying to do, it is potentially possible to do all of that with the SQL.

I'm swamped, but will try to check this tomorrow. Sorry to take so long to reply.

Have a great day! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!