How to find out duplicate records from multiple tables in MS Access based on month and the below columns ?
Name, Text, Description, TestDescription
select [table1].[Name], [table1].[Text], [table1].[Description], [table1].[TestDescription]
From [table1]
UNION ALL
select [table2].[Name], [table2].[Text], [table2].[Description], [table2].[TestDescription]
from [table2]
WHERE Table1.month IN ("April","May") and Table2.month IN ("April","May")
group by [table1].[Name], [table1].[Text], [table1].[Description], [table1].[TestDescription]
having count(*) > 1;
Each SELECT in a UNION is it's own seperate query, so you can't do two WHERE clauses at the bottom. Each part must have it's own WHERE and each part doesn't know about the other part (it's out of context). Instead treat the entire UNION result set as a subquery to do your GROUP BY... HAVING test:
SELECT *
FROM
(
SELECT [table1].[Name], [table1].[Text], [table1].[Description], [table1].[TestDescription]
FROM [table1]
WHERE Table1.month IN ("April","May")
UNION ALL
SELECT [table2].[Name], [table2].[Text], [table2].[Description], [table2].[TestDescription]
FROM [table2]
WHERE Table2.month IN ("April","May")
) AS myUnion
GROUP BY [myUnion].[Name], [myUnion].[Text], [myUnion].[Description], [myUnion].[TestDescription]
HAVING COUNT(*) > 1;
Based on your comments to the first answer posted, if you are actually looking to find records which appear in both tables based on the four fields you mention, you could use a join, e.g.:
select t1.*
from
table1 t1 inner join table2 t2 on
t1.name = t2.name and
t1.text = t2.text and
t1.description = t2.description and
t1.testdescription = t2.testdescription
where
t1.month in ("April", "May")
