Simulate Full Outer Join in MS Access

Sometimes a problem arises in the condition when there are two reports from two separate sources, the number of fields in each report may vary but there are at least 5 fields that are definitely same in both reports, and the data in these 5 fields should be an identical match. And we want to identify where the data does not match. And we want to bring both records back, in case of mismatch.

This could be achieved using "Full Outer Join". The following article guides you that how to simulate full outer join in MS Access.

The data currently present in tables PARTY and REG.

The following fields in the PARTY table are compared with the fields in the REG table.


  • 'ID_OtherComp’ in the table PARTY should match field "UTI_Extract’ in the REG table.
  • "Party1_OtherComp in the table PARTY should match field "CP1_Extract’ in the REG table.
  • 'Party2_OtherComp in the table PARTY should match field "CP2_Extract’ in the REG table.
  • "Val_OtherComp’ in the table PARTY should match field "MarketVal_Extract’ in the REG table.
  • Follow the steps mention below to simulate full outer join:-

    Step1:-Table is created with name "PARTY’, having 4 fields with 1 primary field "ID_OtherComp’

    Simulate Full Outer Join in MS Access Fig-1.1

    Fig-1.1

    Step2:-Table is created with name "REG’, having 4 fields with 1 primary field "UTI_Extract’.

    Simulate Full Outer Join in MS Access Fig-1.2Fig-1.2

    Step3:-Create a query, this will provide all the mismatched records that are not present in second table.

    Simulate Full Outer Join in MS Access Fig-1.3Fig-1.3

    Step4:-Create a query, this will provide all the mismatched records that are not present in first table.

    Simulate Full Outer Join in MS Access Fig-1.4Fig-1.4

    Step5:-Final query is created, which reviews each row of data and returns records with any mismatch from both tables. Basically it is an UNION of first and second query.

    Simulate Full Outer Join in MS Access Fig-1.5Fig-1.5


    DISCLAIMER

    It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.