This is my first time posting in this website. I am new to Microsoft Access, I mainly work with Excel. I have been looking for a formula or procedure to ease a task but have been unable to find a solution through excel. I was told MS Access was way better at handling databases. Please excuse any syntax error as I am not very familiar with Access. I've been trying to compare two tables with specific guidelines but seem to not be able to automate the process. This is a 1000+ entry table so it takes some time. I would like to know if it was possible to automate the process with Access.
The below databases come from two different systems that should be talking to each other, but do not always match. What I am attempting to do is to look for mismatches by setting Database 1 and Database 2 side by side, matching Sector 1 from both databases, then comparing the mismatches between sector 2, 3, 4, and 5... This is only a representation of what the tables look like in a small scale.
This second example was created manually. Would like to learn how to use Excel PowerQuerty to create Database 1 and Database 2 as shown from Database 1 and Database 2 from above example. The Match / No Match is just for my own benefit to see where the missmatches are to be corrected and will be created manually.
I'd like to learn how to make it so that when comparing Database 1 and Database 2, matching by Sector 1; I could have in either Database 1 or 2 a row created as a blank depicting either a repeat occurred or that there is no such entry on opposite database. The example below has a blank row on Database 2 for row # 1. Entry in Database 1 for row 1 does not exist in Database 2, therefore a blank was created for Database 2. The same goes for rows 15, 16, and 17; Database 1 and Database 2. A blank row was created for row 16 and 17 on Database 2 since data on row 16 and 17 for Database 1 was repeated and did not exist in Database 2. Any help would be greatly appreciated