1

Im struggling with writing a query and infact do not know which query is relevant for the task (Union, Inner/Outer Join etc)

I have a table of data that is revised each week and I need to report on the differences.

i.e if it gets deleted in table 1, table 2 or a field changes.

I have included an image to show the data from the revised table and also what I would like as an output to report on (Ignore the Comments, they are only for reference)

Any help would be appreciated.

enter image description here

Siyual
  • 16,415
  • 8
  • 44
  • 58
FreddyFord
  • 11
  • 1
  • Looks like you'd want to use a FULL OUTER join. Include records from both tables where the key of either table is null However access doens't appear to support them. So you need to do left join twice and a union http://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access – xQbert May 31 '16 at 14:17

2 Answers2

0

FULL OUTER JOIN to find any rows from table 1 or table 2 and align them whenever possible then you can use a CASE to create the comment column based on the comparison of AREA, or NAME being null.

But MS ACCESS doesn't have FULL JOIN so we need LEFT JOIN UNION RIGHT JOIN. Also CASE statement is VB syntax, use switch

SELECT
  t1.*,
  t2.*,
  switch(
    t2.name IS NULL,'IN TABLE 1 ONLY',
    t1.area <> t2.area,'IN TABLE 1 AND 2 SAME NAME BUT AREA DIFFERENT IN TABLE 2',
    true,'IN TABLE 1 AND 2 AND EQUAL')
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.name = t2.name
UNION
SELECT
  t1.*,
  t2.*,
  switch(
    t1.name IS NULL,'IN TABLE 2 ONLY, OMITTED IN TABLE 1',
    t1.area <> t2.area,'IN TABLE 1 AND 2 SAME NAME BUT AREA DIFFERENT IN TABLE 2',
    true,'IN TABLE 1 AND 2 AND EQUAL')
FROM table1 AS t1
RIGHT JOIN table2 AS t2
ON t1.name = t2.name
Stavr00
  • 3,219
  • 1
  • 16
  • 28
0

I don't believe access supports full outer joins ...

so... we use a left join and a right join and a union along with a case statement.

Select A.*, case when T1.Name=T2.Name and T1.Area=T2.Area then 'In T1 and T2 and Equal'
                 when T2.Name is null then 'In T1 Only'
                 when T1.Name is null then 'in T2 Only,omitted in T1'  
                 when T1.Name = T2.Name and T1.Area<> T2.Area then 'In T1 and T2 Different area' from (
Select t1.*, T2.*
FROM table1 T1
LEFT JOIn table2 T2
 on T1.Name = T2.Name and T1.Area = T2.Area
UNION
Select t1.*, T2.*
FROM table1 T1
RIGHT JOIN table2 T2
 on T1.Name = T2.Name and T1.Area = T2.Area) A
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • You are correct about the `full outer join` , thanks for pointing it out. If you noticed he wanted `t1` values to be nulls when only `t2` is available, and when only `t1` is available to use `t1` values for `t2` as well. Also, you can use `Nz()` instead of this cases. – sagi May 31 '16 at 14:34