Comparing Two Tables SQL UNION ALL 2

[TJVFree]

I’m trying to compare two tables using some sql I found. The error message I’m receiving is:

The select statement includes a reserved word or an argument name that is Misspelled or missing, or the punctuation is incorrect

The code I used is at the bottom.
Please let me know if you need more information or if you’re able to determine the problem from just look at the code.

Thanks for your help


[sql]SELECT MIN(Project_Compare) as Table1, App_Num,
From
(
SELECT 'Table A' as Project_Watch_list_9_14_11_tbl, App_Num
From A
UNION ALL
SELECT 'Table B' as Project_Watch_list_9_15_11_tbl, App_Num
FROM B
) tmp
Group By app_num
Having Count (*) = 1
Order By App_Num[/sql]


[SQL]SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID[/sql]

 

[SkipVought]

hi,

First, there is no [SQL]...[/SQL] TGML tag, as you can clearly see.

So you have two tables ih your DB named A & B, yes?

Part of debugging your problem is doin testing of parts of your code. Do either of these run alone run sucessfully?

SELECT 'Table A' as Project_Watch_list_9_14_11_tbl, App_Num
From A

SELECT 'Table B' as Project_Watch_list_9_15_11_tbl, App_Num
FROM B

SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
FROM A

SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
FROM B

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

You haven't included or created Project_Compare in you tmp SQL statement.
Also, the second "AS Project_Watch_list_9_15_11_tbl" is ignored as the column will be named based on the first SELECT which is Project_Watch_list_9_14_11_tbl but I expect you want it to be Project_Compare.

There is also an extra comma at the end of the first line.

Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Yes,

The first two do.

SELECT 'Table A' as a, App_Num
From A
Union all
SELECT 'Table B' as B, App_Num
FROM B[\code]

 

[TJVFree]

Now I'm trying to add the first and last part to the middle part. What am I missing?

SELECT MIN(Project_Compare) as Table1, App_Num,
From
(

SELECT 'Table A' as a, App_Num
From A
Union all
SELECT 'Table B' as B, App_Num
FROM B


) tmp
Group By app_num
Having Count (*) = 1
Order By App_Num[/sql]

 

[TJVFree]

Duane and Skip,

Is this what you would recommend?

Yesterday day I will run a new report and need to track the changes from day to day. I have about 30 fields that I will be needing to compare. I’ll be needing to add each day but only care to see the ones that have had changes. You both have helped me in the past and I highly respect your opinion

Thanks Corey

 

[PHV]

SELECT MIN(Project_Compare) as Table1, App_Num
From (
SELECT 'Table A' as Project_Compare, App_Num From A
Union all SELECT 'Table B', App_Num FROM B
) tmp
Group By app_num
Having Count(*) = 1
Order By App_Num

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TJVFree]

Thanks PHV....It workes :0-)

 

[TJVFree]

Would I just add another line when I add another table?

SELECT MIN(Project_Compare) as Table1, App_Num
From (

SELECT 'Table A' as Project_Compare, App_Num From A
Union all 
SELECT 'Table B', App_Num FROM B
Union all
SELECT 'Table C', App_Num FROM C
) 
tmp
Group By app_num
Having Count(*) = 1
Order By App_Num

 

[PHV]

Your last posted SQL code seems perfect for me.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TJVFree]

Thanks PHV,

It ran, but had less fields then the first one, and wanted to make sure I understood what it was suppose to do.

So the final sql I posted will only show me the app_Num lines that don’t match. Right?