Comapring two tables in MSAccess

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

Comapring two tables in MSAccess

Author

Topic

pradeepjk
Starting Member

2 Posts

Posted - 2008-02-17 : 04:45:39

Hi Guys,

I am facing an issue while comparing the two identical tables , the code as below

SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.Ref, tmp.Underlying, tmp.MATURITY, tmp.Settled, tmp.PRINCIPAL, tmp.NPV, tmp.DELTA, tmp.PAY_RECEIVE, tmp.cdb_product_type, tmp.Book, tmp.RISKDATE
FROM [SELECT 'table1' as TableName, table1.Ref,table1.Underlying,table1.MATURITY,table1.Settled,table1.PRINCIPAL,table1.NPV,table1.DELTA,table1.PAY_RECEIVE,table1.cdb_product_type,table1.Book,table1.RISKDATE

FROM table1

UNION ALL

SELECT 'table2' as TableName, table2.ID,table2.Ref,table2.Underlying,table2.MATURITY,table2.Settled,table2.PRINCIPAL,table2.NPV,table2.DELTA,table2.PAY_RECEIVE,table2.cdb_product_type,table2.Book,table2.RISKDATE

FROM table2

]. AS tmp
GROUP BY tmp.Ref, tmp.Underlying, tmp.MATURITY, tmp.Settled, tmp.PRINCIPAL, tmp.NPV, tmp.DELTA, tmp.PAY_RECEIVE, tmp.cdb_product_type, tmp.Book, tmp.RISKDATE
HAVING (((Count(*))=2))
ORDER BY tmp.ID;

This should ideally return the mismatch data by comparison, but neither of the COUNT(*)=1 or 2 returning any value.
table1 and table2 have same colums and datatypes.

please help me !

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-17 : 05:13:32

Tried

HAVING MIN(TableName) <> MAX(TableName)

E 12°55'05.25"
N 56°04'39.16"

pradeepjk
Starting Member

2 Posts

Posted - 2008-02-17 : 23:57:03

Thanks a lot for reply , but i am getting an error with the ID as ID is not the column of the tables. Pls help me to find what this ID is.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources