By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

Database Matching

P: 2
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

Attached Images
File Type: jpg 1.jpg (57.0 KB, 218 views)
File Type: jpg 2.jpg (82.2 KB, 149 views)
Attached Files
File Type: xlsx Tables.xlsx (22.0 KB, 60 views)
Mar 30 '18 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,418
Judging by the pictures you're not talking about databases at all, but probably tables. You should understand that tables contain a set of records. Sets are positionally independent; that is they may be displayed in various orders, but are not held in any way such that they are relative to each other. IE. Whatever order they may be stored in is irrelevant as far as set theory and databases go.

I think you need to be much clearer about what it is you expect to achieve. Unfortunately your pictures are of too low quality to be of much help, but words are the tool you should focus on using to describe your situation clearly. Please don't make the mistake of using too many as that'll just frighten off any potential helpers ;-) Clear and succinct is what you're aiming for.
Mar 31 '18 #2

P: 2
Thank you for taking the time to post a reply. Attached to the original post is an excel file with a better explanation. I hope it explains my purpose.

Thank you
Mar 31 '18 #3

Expert Mod 15k+
P: 31,418
Attached to the original post is an excel file with a better explanation.
Then it needs to be posted in the thread. Not left for users to find in an attachment. However, I very much doubt it will be adequate if it was done at a time when your understanding of the meaning of the important words was so far off.

Feel free to use it in the post if you believe it will help, but don't be surprised if the thread gets very little traction, if the words give a different meaning from what you intend. I suggest simply doing as originally instructed will work best for you.
Mar 31 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.