need to find difference in 2 table fields/columns both the same and different

[DougP]

I was making a form to lets the users pick each table then have a compare button to return the results into to sub forms. subform one shows common fields in both tables. subform2 has Table1 missing fields , and subform 3 has Table2 missing fields.
Example:
So both Table1 and Table2 have "Report" and "Date" fields.
Table1 does not have "Name" field
Table2 does not have the "Total" field.

Is there a way to make a SQL statement to show the differences?
I have this showing the fields that are the same.

SELECT TableDefs.Fields FROM TableDefs " & _
                "GROUP BY TableDefs.Fields " & _
                "HAVING (((TableDefs.Fields) In (SELECT [Fields] FROM " & _
                "[TableDefs] As Tmp GROUP BY [Fields] HAVING Count(*)>1 )));"
    Me.TableDefs_Duplicate_Fields_subform.Form.RecordSource = SQLCompare
    Me.TableDefs_Duplicate_Fields_subform.Requery

DougP

 

[DougP]

Dooh! was it this simple, sorry for self-solve
adding the word NOT and also filtering it down to the table name.
So what I am going to do is put this into a subform under the combobox of Table2
then use the same code except for (((TableDefs.Tablename)="[highlight #8AE234]Table2[/highlight]") and put that inder the combox box for Table1, so each subform shows what is NOT in the other table.

SELECT TableDefs.Tablename, TableDefs.Fields
FROM TableDefs
GROUP BY TableDefs.Tablename, TableDefs.Fields
HAVING (((TableDefs.Tablename)="[highlight #8AE234]Table1[/highlight]") AND ((TableDefs.Fields) [highlight #FCE94F]Not[/highlight] In (SELECT [Fields] FROM 
               [TableDefs] As Tmp GROUP BY [Fields] HAVING Count(*)>1 )));

DougP