Compare two queries with the same structure and export the differences

[btr]

Hello everyone!

I have two select queries with identical structures, joined on a key field.
I want to compare the two, and extract the keys of the records with differences into another query / table.

For example:
i.imgur.com/yLxFywh.png
(sorry I don't have enough post count to insert image)

There are two problems:
- The fields in query 1 and 2 will change based on selection the user made on another form, except for the Key field and the Date field. The structures of these two queries will always be identical though.
- All fields need to be compared except for the Key field and the Date field.

I've been stuck with this for a while now; I think it should not be too complicated, but I'm such a noob at VBA I really hope someone could help me with this. Also, I'd really appreciate it if you can be a bit detailed in your guidance, cause I'm really not good at VBA >.<

Thanks in advance!

 

[bob fitz]

Perhaps you could use the wizard to create a Find Unmatched Query

 

[btr]

Perhaps you could use the wizard to create a Find Unmatched Query

But the fields in the queries will change dynamically so that won't work?

 

[sneuberg]

As a union query without the ALL keyword will combine records that are identical any non VBA solution will probably use that, but it may be simpler than that. Please post the SQL of the queries so that we can look at what might be possible.

Also do you want the records where the key matches but the other data except for the date doesn't match?

 

[btr]

As a union query without the ALL keyword will combine records that are identical any non VBA solution will probably use that, but it may be simpler than that. Please post the SQL of the queries so that we can look at what might be possible.

Also do you want the records where the key matches but the other data except for the date doesn't match?

The two queries 1 and 2 pulled data from query0.

Query 0 is built based on selection from a form. Here's the code for query 0:

For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value Then
strSQL = strSQL & "[" & ctl.Name & "] ,"
End If
End If
Next

If strSQL = "" Then Exit Sub

CurrentDb.QueryDefs.Delete conQUERY_NAME

On Error GoTo Err_cmdTest_Click

strSQL_2 = "SELECT " & "Date, " & Left$(strSQL, Len(strSQL) - 2) & " FROM Table0;"

'Create the QueryDef Object
Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)​

I'm sorry it's so confusing

 

[sneuberg]

Ok I understand Query 0 but what are queres 1 and 2? Please post the SQL for them or the code that creates their SQL.

 

[Isskint]

Perhaps you could use the wizard to create a Find Unmatched Query

That was good advice. Just amend the query slightly and voila, SQL of a query that will achieve your aim of identifying the differences.

SELECT Query1.Key
FROM Query1 LEFT JOIN Query2 ON Query1.[Key] = Query2.[Key]
WHERE (((Query2.F1)<>[Query1].[F1])) OR (((Query2.F2)<>[Query1].[F2])) OR (((Query2.F3)<>[Query1].[F3]));

Just run this (or an amended version) after you have messed about with the querydefs of query1+2

 

[btr]

Ok I understand Query 0 but what are queres 1 and 2? Please post the SQL for them or the code that creates their SQL.

Sorry I should be more clear >.<
Query 1 and 2 pull all fields from Query 0 based on Date condition.

Query 1:

SELECT DISTINCT [Query 0].*
FROM [Query 0]
WHERE ((([Query 0].Date)=[Forms]![Home]![Date1]));​

Query 2:

SELECT DISTINCT [Query 0].*
FROM [Query 0]
WHERE ((([Query 0].Date)=[Forms]![Home]![Date2]));​

SQL of a query that will achieve your aim of identifying the differences.

SELECT Query1.Key
FROM Query1 LEFT JOIN Query2 ON Query1.[Key] = Query2.[Key]
WHERE (((Query2.F1)<>[Query1].[F1])) OR (((Query2.F2)<>[Query1].[F2])) OR (((Query2.F3)<>[Query1].[F3]));

No, the fields in the queries are not fixed and will changed dynamically - there might be 3 fields or 10 fields based on the source query (query 0), so that won't work.

 

[Isskint]

No, the fields in the queries are not fixed and will changed dynamically - there might be 3 fields or 10 fields based on the source query (query 0), so that won't work.

So use this as a basis to create a query on the fly, as you do now, adding the relevant number of extra

OR (((Query2.F??)<>[Query1].[F??]))

 

[sneuberg]

You mentioned a Key field in your original post. With the exception of the date it appears the fields in query 0 are determined by the check boxes. Is the key field one of those check boxes and doesn't this mean query 0 may not contain the Key field?

To do this I think you will need a primary key in the query.

 

[btr]

So use this as a basis to create a query on the fly, as you do now, adding the relevant number of extra

OR (((Query2.F??)<>[Query1].[F??]))

The fields' name won't actually be [fixed name] + [increment number] though I just called them F1, F2 etc... due to confidential problem. There are no rules / patterns for field names.

What I need is something completely dynamic and does not rely on field names at all I think it can only be achieved through VBA.

You mentioned a Key field in your original post. With the exception of the date it appears the fields in query 0 are determined by the check boxes. Is the key field one of those check boxes and doesn't this mean query 0 may not contain the Key field?

To do this I think you will need a primary key in the query.

Yep, the Key field is also determined by the check boxes as all other fields in the query 0. But the Key field and the Date field should always be chosen on default (it's not in the code yet but I'll include it soon).

The Key field has unique values so it can be used as the primary key, I think.

 

[sneuberg]

Your output would have to be a table but you could open these queries as record sets in VBA and refer to the fields by an index, e.g. rs.Fields(0) and you can get the number of fields, e.g., rs.Fields.Count, but without knowing what or where the primary key is I think you are out of gas.

I suggest adding the primary key to query 0 as a fixed field.

If you do that then I think might play with a union query. The idea is to make a UNION query withe the ALL keyword of the two queries and then put the result of that in a group by query. The records that match will have a count of one while those that didn't will have a count of two. Note: I'm not sure if this works when the field list is something like [Query 0].*

 

[sneuberg]

One more thought. If these dates are on the same form as the checkboxes then I think the easiest way to do this would be to generate a specific query for it as IssKint suggested.