comparing two tables in Access 2003

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

comparing two tables in Access 2003

Author

Topic

associates
Starting Member

31 Posts

Posted - 2007-08-12 : 23:58:00

Hi,

wonder if anyone might be able to help me here. I've been trying to do a comparison between two tables and come across to the codes as follows

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

replaced by

SELECT MIN(TableName) as TableName, Proj_ID, Project_ID, [OLD J_ID], J_ID, [OLD Project_Name],...
FROM
(
SELECT 'Projects_Old A' as TableName, A.Proj_ID, A.Project_ID, A.[OLD J_ID], A.J_ID, ...
FROM A
UNION ALL
SELECT 'Projects_New B' as TableName, B.Proj_ID, B.Project_ID, B.[OLD J_ID], B.J_ID, ...
FROM B
) tmp
GROUP BY Proj_ID, Project_ID, [OLD J_ID], J_ID, [OLD Project_Name],...
HAVING COUNT(*) = 1
ORDER BY Proj_ID

My question is i got error messages showing up either saying it didn't recognize what A is or syntax error. Is this only for SQL Server? Can i still get it to work in Access 2003?

Thank you in advance

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 10:53:38

in access there is a inbuilt Unmatched Query Wizard which
which lets you do this step by step.

Click on Queries in the Objects Pane on the left side.
Then Click on new and select Find Unmatched Query Wizard Then select the first table and then the second

then you can do the vaice versa in next query and then take the union of both

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources