Query to Find differences

L

LeoM

Member
Local time
Today, 20:13
Joined
Jun 22, 2023
Messages
84
Good afternoon everyone.
I have the following question please, if you can support me. I have to create a query which return the differences in one field between 2 tables.
I give the example hoping i'm clear:

I have Table-1 as follow:
EquiManEquiModel numberObject typeClassCharacteristicChar Value
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP02150000.0
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP056OIL
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP132415.0
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3288NONE
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3370
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP40214.80
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP4253
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP502450.000
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP698
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP704848.00
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP705720.06.2022
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP70662.40

Then i have Table-2 as follow:

MANUFACTURERMODEL_NUMBERTECHNICAL_OBJECT_TYPEEQUIPMENT_CLASSATTRIBUTE_TYPEVALUE_TYPE
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP056OIL
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP132415
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3288NON HAZARDOUS
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3288NONE
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP4024.80
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP4025
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP4253
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP502400
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP69855
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP704848
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP70662.40

Keeping as key the following :

EquiMan=MANUFACTURER
EquiModel number=MODEL_NUMBER
Object type=TECHNICAL_OBJECT_TYPE
Class=EQUIPMENT_CLASS
Characteristic=ATTRIBUTE_TYPE

the result of the query should give me the records of the Table-1 where the "Char Value" is not available in the field "VALUE_TYPE" of Table-2. Below the result i should have:

RESULT
EquiManEquiModel numberObject typeClassCharacteristicChar Value
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP132415.0
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP40214.80
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP502450.000
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP704848.00

Thank you for usual and kindly support.
Cheers
L.
 
Try something like:
SQL: 
SELECT
  t1.*
FROM [Table-1] t1
LEFT JOIN [Table-2] t2
       ON t1.ATTRIBUTE_TYPE = t2.Characteristic
      AND t1.[Char Value] = t2.VALUE_TYPE
WHERE t2.VALUE_TYPE IS NULL
;
 
see FINALQUERY on the demo.
 

Attachments

arnelgp said:
see FINALQUERY on the demo.
Click to expand...
Good morning and thanks first of all. The demo is good but it consider only numbers while i need to control all records (either numbers or char or even date sometime) . Maybe my example confuse because it was just a case that differences was only in numbers. See below a more accurate example to let better understand:

TABLE-1
EquiManEquiModel numberObject typeClassCharacteristicChar Value
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP02150000.0
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP056OIL1
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP132415.0
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3288NONE
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3370


TABLE-2
MANUFACTURERMODEL_NUMBERTECHNICAL_OBJECT_TYPEEQUIPMENT_CLASSATTRIBUTE_TYPEVALUE_TYPE
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP056OIL
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP132415
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3288NON HAZARDOUS
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP3288NONE
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP4024.80

RESULT SHOULD BE
EquiManEquiModel numberObject typeClassCharacteristicChar Value
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP056OIL1
KRISTRON SYSTEMS3MV1-48V-50AECCPEP_ECCPEP132415.0

How to manage in your demo ?
Cheers
 
This is going to take 2 queries. First though, you need to add an autonumber primary key to Table1. Let's call that T1_ID. Then this is how you build the first query:

Make a new query, bring in Table 1 and Table2 and JOIN them on every one of the 6 fields they share. Then bring down T1_ID and save that query as SUB1.

Then build a new query using Table1 and SUB1 and JOIN them on their TI_ID fields and change it to a LEFT JOIN such that every record in Table1 shows and just matches in SUB1. Bring down every field from Table1 and the T1_ID from SUB1. Under SUB1.T1_ID in the criteria area put 'IS NULL'. Run that and you have your desired results.
 
Thank you.
I followed exactly what you suggested with a small modification described at point 1:

1. Since i don't have an auto number primary key in my Table1, i create a temporary table of my Table1 (lets call Table1_Temp) adding all fields of Table1 and the field T1_ID as auto number and (since i'm already use a query) I join the Table2 with the 5 (not 6) fields which is my input for control (from the join remain out only the field "Char Value"). In this way i have my Table1_Temp which became the table to be verified. If don't make this step (to joining the 5 fields instead of the 6 already) in my final result i will also have something which should not be controlled.
2. I create a new query SUB1, in this case as you suggested i have all 6 fields in join between "Table1_Temp" and "Table2" plus T1_ID.
3. I create my final query exactly as you suggested and i got what exactly i need.

Thanks a lot, looks like work fine.
 
maybe you can use a UDF on your query.
 

Attachments

arnelgp said:
maybe you can use a UDF on your query.
Click to expand...
I used it but when i run the query i got Runtime error 3219 - Invalid Operation at the line :

Set rs = CurrentDb.OpenRecordset("SAPRAMZ_V_IMPORTAL_EQUIPMENT_CATALOG", dbOpenTable)

Then i try to change dbOpenTable with dbOpenDynaset and i got error Runtime error 3251 - Operation not supported for this type of object at the line:

.Index = "IDX"

I believe is a problem of reference since in your example db work fine, following my linked references:

1729073317996.png


Any idea?
Cheers
 
you need to Add Index to your Table_2.

idx.png
 
maybe change the code to this and rename Table_2 with the correct table name you have.
Code: 
Public Function IsSimilar( _
                ByVal sEquipt As String, _
                ByVal sModel As String, _
                ByVal sType As String, _
                ByVal sClass As String, _
                ByVal sCharacter As String, _
                ByVal vValue As Variant) As Boolean

     
    Static rs As DAO.Recordset
    If IsNull(vValue) Then
        IsSimilar = True
        Exit Function
    End If
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset( _
                "SELECT * FROM Table_2 Order By [MANUFACTURER], [MODEL_NUMBER], " & _
                "[TECHNICAL_OBJECT_TYPE], [EQUIPMENT_CLASS], [ATTRIBUTE_TYPE];", dbOpenSnapshot, dbReadOnly)
    End If
    With rs
        '.Index = "IDX"
        '.Seek "=", sEquipt, sModel, sType, sClass, sCharacter
        .FindFirst "[MANUFACTURER]='" & sEquipt & "' AND " & _
                "[MODEL_NUMBER]='" & sModel & "' AND " & _
                "[TECHNICAL_OBJECT_TYPE]='" & sType & "' AND " & _
                "[EQUIPMENT_CLASS]='" & sClass & "' AND " & _
                "[ATTRIBUTE_TYPE]='" & sCharacter & "'"
        If Not .NoMatch Then
            Do While True
                If vValue & "" = !vALUE_TYPE & "" Then
                    IsSimilar = True
                    Exit Do
                End If
                .MoveNext
                If .EOF Then
                    Exit Do
                End If
                If !MANUFACTURER <> sEquipt Or _
                   !MODEL_NUMBER <> sModel Or _
                   !TECHNICAL_OBJECT_TYPE <> sType Or _
                   !EQUIPMENT_CLASS <> sClass Or _
                   !ATTRIBUTE_TYPE <> sCharacter Then
                   Exit Do
                End If
            Loop
        Else
            IsSimilar = True
        End If
    End With
End Function
 
arnelgp said:
maybe change the code to this and rename Table_2 with the correct table name you have.
Code: 
Public Function IsSimilar( _
                ByVal sEquipt As String, _
                ByVal sModel As String, _
                ByVal sType As String, _
                ByVal sClass As String, _
                ByVal sCharacter As String, _
                ByVal vValue As Variant) As Boolean

    
    Static rs As DAO.Recordset
    If IsNull(vValue) Then
        IsSimilar = True
        Exit Function
    End If
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset( _
                "SELECT * FROM Table_2 Order By [MANUFACTURER], [MODEL_NUMBER], " & _
                "[TECHNICAL_OBJECT_TYPE], [EQUIPMENT_CLASS], [ATTRIBUTE_TYPE];", dbOpenSnapshot, dbReadOnly)
    End If
    With rs
        '.Index = "IDX"
        '.Seek "=", sEquipt, sModel, sType, sClass, sCharacter
        .FindFirst "[MANUFACTURER]='" & sEquipt & "' AND " & _
                "[MODEL_NUMBER]='" & sModel & "' AND " & _
                "[TECHNICAL_OBJECT_TYPE]='" & sType & "' AND " & _
                "[EQUIPMENT_CLASS]='" & sClass & "' AND " & _
                "[ATTRIBUTE_TYPE]='" & sCharacter & "'"
        If Not .NoMatch Then
            Do While True
                If vValue & "" = !vALUE_TYPE & "" Then
                    IsSimilar = True
                    Exit Do
                End If
                .MoveNext
                If .EOF Then
                    Exit Do
                End If
                If !MANUFACTURER <> sEquipt Or _
                   !MODEL_NUMBER <> sModel Or _
                   !TECHNICAL_OBJECT_TYPE <> sType Or _
                   !EQUIPMENT_CLASS <> sClass Or _
                   !ATTRIBUTE_TYPE <> sCharacter Then
                   Exit Do
                End If
            Loop
        Else
            IsSimilar = True
        End If
    End With
End Function
Click to expand...
Good morning. Yes this work excellent, thanks. Little bit slow but because my Table2 is really big but i will try to find a solution to speed-up the process. I have another question if you don't mind: there are Value which should not give errors (so should not appear) because the difference is in the format (we talk about numbers only). For example see below:

Table 1Table 2
[td]
415​
[/td]​
[td]
415.00​
[/td]​
[td]
22​
[/td]​
[td]
22.00​
[/td]​
[td]
30000​
[/td]​
[td]
30000.00​
[/td]​

These numbers are exactly the same but the user, using excel, sometime use as number with decimal. There is a way (in the same script) to avoid to get showed such differences which are not differences ? Thank you
 
change the code to this:

Code: 
Public Function IsSimilar( _
                ByVal sEquipt As String, _
                ByVal sModel As String, _
                ByVal sType As String, _
                ByVal sClass As String, _
                ByVal sCharacter As String, _
                ByVal vValue As Variant) As Boolean

     
    Static rs As DAO.Recordset
    If IsNull(vValue) Then
        IsSimilar = True
        Exit Function
    End If
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset( _
                "SELECT * FROM Table_2 Order By [MANUFACTURER], [MODEL_NUMBER], " & _
                "[TECHNICAL_OBJECT_TYPE], [EQUIPMENT_CLASS], [ATTRIBUTE_TYPE];", dbOpenSnapshot, dbReadOnly)
    End If
    With rs
        '.Index = "IDX"
        '.Seek "=", sEquipt, sModel, sType, sClass, sCharacter
        .FindFirst "[MANUFACTURER]='" & sEquipt & "' AND " & _
                "[MODEL_NUMBER]='" & sModel & "' AND " & _
                "[TECHNICAL_OBJECT_TYPE]='" & sType & "' AND " & _
                "[EQUIPMENT_CLASS]='" & sClass & "' AND " & _
                "[ATTRIBUTE_TYPE]='" & sCharacter & "'"
        If Not .NoMatch Then
            Do While True
                If IsNumeric(vValue) Then
                    If Val(vValue) = Val(!Value_Type & "") Then
                        IsSimilar = True
                        Exit Do
                    End If
                Else
                    If vValue & "" = !Value_Type & "" Then
                        IsSimilar = True
                        Exit Do
                    End If
                End If
                .MoveNext
                If .EOF Then
                    Exit Do
                End If
                If !MANUFACTURER <> sEquipt Or _
                   !MODEL_NUMBER <> sModel Or _
                   !TECHNICAL_OBJECT_TYPE <> sType Or _
                   !EQUIPMENT_CLASS <> sClass Or _
                   !ATTRIBUTE_TYPE <> sCharacter Then
                   Exit Do
                End If
            Loop
        Else
            IsSimilar = True
        End If
    End With
End Functionp/code]
 
e
arnelgp said:
change the code to this:

Code: 
Public Function IsSimilar( _
                ByVal sEquipt As String, _
                ByVal sModel As String, _
                ByVal sType As String, _
                ByVal sClass As String, _
                ByVal sCharacter As String, _
                ByVal vValue As Variant) As Boolean

    
    Static rs As DAO.Recordset
    If IsNull(vValue) Then
        IsSimilar = True
        Exit Function
    End If
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset( _
                "SELECT * FROM Table_2 Order By [MANUFACTURER], [MODEL_NUMBER], " & _
                "[TECHNICAL_OBJECT_TYPE], [EQUIPMENT_CLASS], [ATTRIBUTE_TYPE];", dbOpenSnapshot, dbReadOnly)
    End If
    With rs
        '.Index = "IDX"
        '.Seek "=", sEquipt, sModel, sType, sClass, sCharacter
        .FindFirst "[MANUFACTURER]='" & sEquipt & "' AND " & _
                "[MODEL_NUMBER]='" & sModel & "' AND " & _
                "[TECHNICAL_OBJECT_TYPE]='" & sType & "' AND " & _
                "[EQUIPMENT_CLASS]='" & sClass & "' AND " & _
                "[ATTRIBUTE_TYPE]='" & sCharacter & "'"
        If Not .NoMatch Then
            Do While True
                If IsNumeric(vValue) Then
                    If Val(vValue) = Val(!Value_Type & "") Then
                        IsSimilar = True
                        Exit Do
                    End If
                Else
                    If vValue & "" = !Value_Type & "" Then
                        IsSimilar = True
                        Exit Do
                    End If
                End If
                .MoveNext
                If .EOF Then
                    Exit Do
                End If
                If !MANUFACTURER <> sEquipt Or _
                   !MODEL_NUMBER <> sModel Or _
                   !TECHNICAL_OBJECT_TYPE <> sType Or _
                   !EQUIPMENT_CLASS <> sClass Or _
                   !ATTRIBUTE_TYPE <> sCharacter Then
                   Exit Do
                End If
            Loop
        Else
            IsSimilar = True
        End If
    End With
End Functionp/code]
Click to expand...
Excellent!
Thank you for kindly support.
Cheers.
 
Gasman said:
Try adding indices for the required fields?
Click to expand...
Thank you but as i mentioned above i can't add any index since that is a view table in Oracle. Will find another way even if already now is more or less ok.
 
LeoM said:
e

Excellent!
Thank you for kindly support.
Cheers.
Click to expand...
Good morning, i was doing some test and i got error 3077 - Syntax Error (missing operator) at the following point:

.FindFirst "[MANUFACTURER]='" & sEquipt & "' AND " & _
"[MODEL_NUMBER]='" & sModel & "' AND " & _
"[TECHNICAL_OBJECT_TYPE]='" & sType & "' AND " & _
"[EQUIPMENT_CLASS]='" & sClass & "' AND " & _
"[ATTRIBUTE_TYPE]='" & sCharacter & "'"

with the following record:

[MANUFACTURER]='ATLAS COPCO' AND [MODEL_NUMBER]='SD'H'' AND [TECHNICAL_OBJECT_TYPE]='VEDR' AND [EQUIPMENT_CLASS]='EP_F_GENERAL' AND [ATTRIBUTE_TYPE]='EP_FL002'

Is it possible because the [MODEL_NUMBER] include 'SD'H'' ?

Any idea how to fix ?

Thank you,
Cheers
 
try:

Code: 
..
AND [MODEL_NUMBER]='" & Replace$(sMdel, "'","''") & "' AND " & _

or add a function to your form:
Code: 
Private Function fnSingleQuote(Byval sString As String) As String
fnSingleQuote = Replace$(sString, "'", "''")
End Function

then you change the .FindFirst

Code: 
.FindFirst "[MANUFACTURER]='" & fnSingleQuote(sEquipt) & "' AND " & _
"[MODEL_NUMBER]='" & fnSingleQuote(sModel) & "' AND " & _
"[TECHNICAL_OBJECT_TYPE]='" & fnSingleQuote(sType) & "' AND " & _
"[EQUIPMENT_CLASS]='" & fnSingleQuote(sClass) & "' AND " & _
"[ATTRIBUTE_TYPE]='" & fnSingleQuote(sCharacter) & "'"
 
Sometimes I like to use a helper column. Make a query and concatenate all the columns in both tables and call it KEY or something. Then using the query wizard, make a query with the "Find Unmatched Query Wizard" option and use the KEY field as the matching/difference criteria.
 
Last edited:
You must log in or register to reply here.

Similar threads

L
Solved Query To Find Something Thats Not There
Replies
7
Views
1,038
plog
P
isladogs
  • Locked
How to remove 'deleted database objects' from the MSysObjects table
Replies
3
Views
13,298
Louverril
L
Rx_
  • Locked
How to Build a Business Rules Engine
Replies
0
Views
8,612
Rx_
Rx_
T
Trying to get RowSource for a combo box based on the value of a different object
Replies
5
Views
2,135
tfurnivall
T
H
Using Access 2010 to send bulk e-mail each with a unique PDF Attachment
Replies
3
Views
5,163
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom