Query to Find differences

LeoM · Tuesday at 1:06 PM

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:

EquiMan	EquiModel number	Object type	Class	Characteristic	Char Value
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP021	50000.0
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP056	OIL
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP132	415.0
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3288	NONE
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3370
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP402	14.80
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP425	3
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP502	450.000
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP698
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP7048	48.00
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP7057	20.06.2022
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP7066	2.40

Then i have Table-2 as follow:

MANUFACTURER	MODEL_NUMBER	TECHNICAL_OBJECT_TYPE	EQUIPMENT_CLASS	ATTRIBUTE_TYPE	VALUE_TYPE
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP056	OIL
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP132	415
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3288	NON HAZARDOUS
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3288	NONE
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP402	4.80
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP402	5
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP425	3
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP502	400
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP698	55
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP7048	48
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP7066	2.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
EquiMan	EquiModel number	Object type	Class	Characteristic	Char Value
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP132	415.0
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP402	14.80
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP502	450.000
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP7048	48.00

Thank you for usual and kindly support.
Cheers
L.

cheekybuddha · Tuesday at 1:24 PM

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
;

arnelgp · Tuesday at 1:27 PM

see FINALQUERY on the demo.

LeoM · Wednesday at 5:39 AM

arnelgp said:
see FINALQUERY on the demo.

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

EquiMan	EquiModel number	Object type	Class	Characteristic	Char Value
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP021	50000.0
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP056	OIL1
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP132	415.0
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3288	NONE
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3370

TABLE-2

MANUFACTURER	MODEL_NUMBER	TECHNICAL_OBJECT_TYPE	EQUIPMENT_CLASS	ATTRIBUTE_TYPE	VALUE_TYPE
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP056	OIL
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP132	415
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3288	NON HAZARDOUS
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP3288	NONE
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP402	4.80

RESULT SHOULD BE

EquiMan	EquiModel number	Object type	Class	Characteristic	Char Value

KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP056	OIL1
KRISTRON SYSTEMS	3MV1-48V-50A	ECCP	EP_ECCP	EP132	415.0

How to manage in your demo ?
Cheers

plog · Wednesday at 6:07 AM

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.

LeoM · Wednesday at 7:55 AM

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.

arnelgp · Wednesday at 8:41 AM

maybe you can use a UDF on your query.

LeoM · Wednesday at 11:09 AM

arnelgp said:
maybe you can use a UDF on your query.

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:

Any idea?
Cheers

arnelgp · Wednesday at 11:57 AM

you need to Add Index to your Table_2.

LeoM · Wednesday at 12:28 PM

arnelgp said:
you need to Add Index to your Table_2.

View attachment 116533

My Table_2 is an ORACLE view table which is linked to my DB and i don't have any right to modify the structure. No else i can think to do a local copy because is huge table.

arnelgp · Wednesday at 12:44 PM

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

LeoM · 2024-10-17T04:51:15+0100

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

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 1	Table 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

arnelgp · 2024-10-17T07:09:00+0100

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]

LeoM · 2024-10-17T08:15:46+0100

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]

Excellent!
Thank you for kindly support.
Cheers.

Gasman · 2024-10-17T10:38:29+0100

i will try to find a solution to speed-up the process

Try adding indices for the required fields?

Query to Find differences

LeoM

Member

cheekybuddha

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Attachments

LeoM

Member

plog

Banishment Pending

LeoM

Member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

LeoM

Member

arnelgp

..forever waiting... waiting for jellybean!

LeoM

Member

arnelgp

..forever waiting... waiting for jellybean!

LeoM

Member

arnelgp

..forever waiting... waiting for jellybean!

LeoM

Member

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread