Query different fields

[Middlemarch]

If there's two tables but one may have some fields that differ can I see this in a query?


There is a common field called "MyID" which would limits the search to one row in each table.

[Bob Fitz]

Have you tried using "Find Unmatched Query Wizard"

[xps35]

Or maybe

SELECT T1.F1, T2.F1 FROM T1 INNER JOIN T2 ON T1.MyID = T2.MyID
WHERE T1.F1 <> T2.F1

[Middlemarch]

I did try the query wizard but I couldn't get it to output anything - when there are actually 460 differences.
xps35 what should F1 be? A field? Does this mean you must include one field that's different? ? What if you don't know and want any/all different fields (where MYID is the same in both)?

[xps35]

F1 is a field name.

As you can see from the very different answers, your question was not entirely clear. Try to explain exactly what you mean using a simple example. What is in the 2 tables and what do you expect as a result?

[Middlemarch]

> F1 is a field name.
Yes I thought so, but what one?
There's 2 tables, possibly same data, possibly not, but identical structure Their contents are a mix of strings, numbers and dates.
One field (at least) is identical, called MyID.
The result would be anything that shows what records differ, where all fields are not identical.
Can it be done?

[CJ_London]

but what one?

You haven't provided any table of field names, so they have been made up. To expand on xps's suggestion

SELECT T1.F1, T2.F1, T1.F2, T2.F2,  T1.F3, T2.F3 FROM T1 INNER JOIN T2 ON T1.MyID = T2.MyID
WHERE T1.F1 <> T2.F1 OR T1.F2<>T2.F2 OR  T1.F3<>T2.F3

Which addresses what you originally you said

but one may have some fields that differ

but now you are saying

where all fields are not identical.

In which case change the OR's to AND's

[Middlemarch]

My thinking was if all fields are not identical there must be some that differ.
But I see it now, every field has to be specified in the query. There's 60 of them so my UDF that loops through each
is probably okay. I had thought a query might be better in some way. Plus worth knowing how to do.
Thanks for the reply.

[CJ_London]

If you have 60 fields you need to compare and you are trying to identify records with a match on ID but one or more of the other fields don’t match, you can hash the values of the record in both tables and simply compare the hash results.

or use vba to build your sql string. Comparing just using vba will be very slow for more than a few hundred records

[Middlemarch]

Sounds interesting, I'll Google hash values unless you want to tell me how it's used.
What I have now is pretty basic

Set a = CurrentDb.OpenRecordset("Select * from table1 where Target = '" & MyID & "';")
    Set b = CurrentDb.OpenRecordset("Select * from table2 where Target = '" & MyID & "';")
    For i = 0 To a.fields.count - 1
        If a(i) <> b(i) Then msg = msg & a(i).Name & " #1= " & a(i) & vbCrLf & b(i).Name & " #2= " & b(i) & vbCrLf
    Next
MsgBox msg

Normally I'd know which to see and a single pair so speed isn't critical.

[CJ_London]

1. There are a number of hash algorithms, choose one you are happy with
2. Create a public function with an array parameter to return a string
3. In the function, loop through the array appending to a string variable
4. hash the string and pass the hashed value back
5. in a query call the hash function passing the required fields as the array

I suggest your query would include both tables joined on the ID, then call the hash function on both tables fields to compare the result

select *
from t1 inner join t2 on t1.id=t2.id
where fnchash(t1.f1, t1.f2….) <> fnchash(t2.f1,t2.f2….)

[Middlemarch]

This is way too advanced for me, I've never heard of hash algorithms or used an alias in a query.
But decided to have a bash and see what happened. Even though the list of instructions, nicely defined as they are, weren't really understood.
I found https://superuser.com/questions/5505...e-a-hash-value which may do the "hashing" and made that fnchash.
And at the risk of looking like an idiot attempted to build the query like so. I doubt any of it is right, confirmed by it returning Too few parameters. Expected 2.
Mainly just wanted you to know I tried.

Sub TyThis()


Dim x As String
Dim y As String
Dim sql As String
Dim r As DAO.Recordset
x = Build("table1")
y = Replace(x, "t1", "t2")


sql = "select * from table1 as t1 inner join table2 as t2 on t1.id=t2.id where fnchash(x) <> fnchash(y)"
Set r = CurrentDb.OpenRecordset(sql)
Stop
End Sub


Function Build(Table)
    Dim tmp As String
    Dim i As Integer
    Dim r As DAO.Recordset
    Set r = CurrentDb.OpenRecordset(Table)
    For i = 0 To r.fields.count - 1
        tmp = tmp & "t1.f" & i & ","
    Next
    Build = Left(tmp, Len(tmp) - 1)
End Function


Public Function fnchash(ByVal sTextToHash As String)


    Dim asc As Object
    Dim enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 5


    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")


    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey


    bytes = enc.ComputeHash_2((TextToHash))
     fnchash = EncodeBase64(bytes)
    fnchash = Left(BASE64SHA1, cutoff)


    Set asc = Nothing
    Set enc = Nothing


End Function


Private Function EncodeBase64(ByRef arrData() As Byte) As String


    Dim objXML As Object
    Dim objNode As Object


    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")


    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text


    Set objNode = Nothing
    Set objXML = Nothing


End Function

[June7]

I think what CJ suggests is code that returns a number value for all of the fields combined for each table. If the values don't match for related records, something is different. It just won't tell you what.

As I understand it, hashing is a technique applied for encrypting data, as is commonly done with passwords, SSNs, and internet traffic. There are different algorithms depending on the level of security desired. If one knows the key for the hashed data, it can be decrypted back to its original text.

A more correct term than "hashing" might be "checksum", as discussed in this thread https://community.spiceworks.com/t/m...ecord/290574/6.

[CJ_London]

Sorry, no I wasn't. Hashing cannot be 'dehashed' without a key - often used for data storage because it reduces the amount of space required and for password authentication (where you compare the hash of the entered password with the stored hash value). See this link for more information
https://cybernews.com/security/hashing-vs-encryption/

Checksums are a completely different thing used to ensure a number cannot be mistyped easily - often used for things like bank account numbers, telephone numbers and the like. You have an accruing number 1234, 1235, 1236 etc and the checksum algorithm such as modulo 11 - (keep dividing by 11 until you are left with a single digit) and adds that number to the end so you end up with (number made up)

12347
12359
12362

so you can never get a number '12346'