?
Solved

Query that shows all differences between two tables.

Posted on 2014-11-25
9
Medium Priority
?
185 Views
Last Modified: 2014-11-26
After I import my new bill of material part list into access.  I need a query that compare that entire table with the entire existing table and create a table with the differences.   Some parts could have been deleted, some added, and some the quantities could have changed, different rev levels, weight change, etc., then I need it to create a table with the differences.   I've managed to create two queries to create two separate tables, a parts added and a parts deleted table.  This, however, isn't exactly what i need.

 Also, after all of that happens, I need to update the existing table with all of the changes.   Can anyone suggest the easiest way to accomplish this?
0
Comment
Question by:Smilesxl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 40465082
If you are going to replace the existing table with the new import, then all this matching is just extra work and isn't necessary.  Just delete the table and replace it.

To actually do the compares, you will need three different queries.
A left join to find adds
A Right join to find deletes
An Inner Join where you compare column by column to identify field differences.
0
 
LVL 2

Author Comment

by:Smilesxl
ID: 40465175
Actually there is additional columns on the the table that are used for data entry, however I suppose I could separate that data out onto a separate table and link the part number.  Not an issue, however, I was hoping to not have to write 20 queries to compare the other 20 or so columns of actual data, there has to be an easier way.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40465240
You don't need 20 queries but you do need the three I described.

I would separate out the data you maintain from the data you import.  It is much cleaner to simply replace the table.  I would only do the matching logic if I actually needed to know what changed.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 2

Author Comment

by:Smilesxl
ID: 40465247
I need to know what changed.
0
 
LVL 2

Author Comment

by:Smilesxl
ID: 40465251
I guess I'm not understanding the inner join.
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40465295
Here is an example from one of my apps.  It only selects rows where there is one or more differences.  It is used to compare several critical fields in two client tables from different applications.  We are getting rid of one of the apps but until the conversion is complete, data entry is done in both systems and this helps us to keep it consistent.  This query references another query that joins the two tables so it is based on only a single table.  In your case, you would join the two tables in the compare query.

SELECT t.pid, t.FPEMS, t.ems, t.FPName, t.FirstName, t.LastName, t.FPClientStatus, t.ClientStatus, t.FPCareMgr, t.FPCMName, t.CareMgr, t.CMName, t.FPRace, t.Race, Date() AS CreateDT
FROM t
WHERE ((t.FPEMS <>[ems] And t.FPEMS <>"000000000") OR t.FPClientStatus <>[ClientStatus] OR t.FPCareMgr <>[CareMgr] OR [t].[FPRace] & ""<>[Race] & "" OR t.FPCMName <>[CMName])
AND (t.FPClientStatus = "Open" OR t.ClientStatus = "Open");
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40465299
hi,

if you want to have a singular process, showing all changes, you should do this by VBA generating a change report table, where all changes are a single records like eg:

part 4711 new record
part 4712 field 3 changed - old value: ..... - new value: .....
part 4712 field 7 changed - old value: ..... - new value: .....
part 4712 field 23 changed - old value: ..... - new value: .....
part 4713 missing record
...
...
...

is that, what you want to achieve ?
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40465318
Here's a picture of the form used to display the differences.  In our case, we have to fix them manually since someone has to research them to determine which is correct.  I hid most of the names.  But you can see a spot of blue text.  Clicking on that will bring the user to the standard maintenance form where he can change the data as needed.  The report is similar.
To get the green highlight, you need to use conditional formatting for each field.  So, to be consistent, I always highlight the Access side of the compare when it is different from FoxPro.
Differences
0
 
LVL 2

Author Closing Comment

by:Smilesxl
ID: 40466736
Thanks.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question