join you have three choices . only include rows matched by field on both tables, incl ALL ROWS from Sheet1 match and unmatch , incl ALL rows frm Sh2

[YANECKC]

in microsoft access you only have three choices in design join. Only include rows matched by field, include ALL ROWS from Sheet1 and matched rows from sheet2, include ALL ROWS from Sheet2 and matched rows from sheet1. How can i include ALL ROWS from both Sheet1 and Sheet2 matched and unmatched ? It would be like clicking on the second and third choices together.

 

[Joe4]

I would do it in a series of queries.
1. Do the first, all rows form Sheet1 with the matches from Sheet2.
2. Create another unmatched query from Sheet2 to Sheet1, which only returns the records from Sheet2 that have no match from sheet.

Between these two queries, you should have ALL the possible records.
Then, perform a Union query between these two queries you just created to get your complete data set.
See: Use a union query to combine multiple queries into a single result - Microsoft Support

Just note that the data in Union Queries is not editable.