I have two tables in MS Access 2016 as shown below:
Table:Table1
Column1 | Column2 | Column3 | Column4
----------------------------------------
1 | A | P | S
1 | B | J | J
1 | C | P | S
1 | D | S | P
2 | E | S | P
2 | F | P | S
2 | G | P | S
2 | H | S | P
2 | K | J | J
Table: Table2
Column5 | Column6 | Column7 | Column8
--------------------------------------------------
1 | Z | i | Supplier
1 | Z | ii | Supplier
1 | Z | iii | Supplier
1 | Z | iv | Supplier
1 | Z | v | Supplier
1 | Z | vi | Joint
1 | Z | vii | Joint
1 | Y | viii| Joint
1 | Y | ix | Supplier
1 | L | x | Supplier
1 | L | xi | Supplier
1 | L | xii | Supplier
2 | W | xx | Joint
2 | W | xxi | Joint
2 | W | xxii| Joint
2 | W | xxiii| Joint
2 | W | xxiv | Joint
2 | M | xv | Supplier
Is there a way to merge the tables as shown below. The rows should not be repeating.If a cell exist in Table2 and not in 1, leave the cell blank in the result
Query Result:
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8
------------------------------------------------------------------------------------------
1 | A | P | S | 1 | Z | i |Supplier
1 | B | J | J | 1 | Z | ii |Supplier
1 | C | P | S | 1 | Z | iii |Supplier
1 | D | S | P | 1 | Z | iv |Supplier
1 | | | | 1 | Z | v |Supplier
1 | | | | 1 | Z | vi |Joint
1 | | | | 1 | Z | vii |Joint
1 | | | | 1 | Y | viii|Joint
1 | | | | 1 | Y | ix |Supplier
1 | | | | 1 | L | x |Supplier
1 | | | | 1 | L | xi |Supplier
1 | | | | 1 | L | xii |Supplier
2 | E | S | P | 2 | W | xx |Joint
2 | F | P | S | 2 | W | xxi |Joint
2 | G | P | S | 2 | W | xxii|Joint
2 | H | S | P | 2 | W | xxiii |Joint
2 | K | J | J | 2 | W | xxiv|Joint
2 | | | | 2 | M | xv |Supplier
Here is your solution :
SELECT Column5 AS Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8
FROM
(SELECT Column1,Column2,Column3,Column4,
@rownumber:=IF(@lastId=Column1,@rownumber+1,1) AS RowNumber,
@lastId:=Column1
FROM Table1,
(Select @rownumber:=0,@lastId:=MIN(Column1) FROM TABLE1 ) AS r) AS Tbl1
RIGHT JOIN
(SELECT Column5,Column6,Column7,Column8,
@rownumber:=IF(@lastId=Column5,@rownumber+1,1) AS RowNumber,
@lastId:=Column5
FROM Table2,
(Select @rownumber:=0,@lastId:=MIN(Column5) FROM TABLE2 ) AS r) AS Tbl2
ON Tbl1.Column1 = Tbl2.Column5
AND Tbl1.RowNumber = Tbl2.RowNumber
Follow the below link for demo: