-2

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;
Ibra
  • 7
  • 3

2 Answers2

0

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;
JNevill
  • 46,980
  • 4
  • 38
  • 63
  • I ran the above SQL it prompt me to enter the value of the month then it retrieved all the duplicate records from both tables. what i want to achieve here is to compare these tables to get the matching records. – Ibra Jul 09 '19 at 06:48
  • @Ibra If the code is prompting for the value of the `month`, then it would imply that your table does not contain a field called `month`. – Lee Mac Jul 09 '19 at 18:48
0

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")
Lee Mac
  • 15,615
  • 6
  • 32
  • 80