Results 1 to 13 of 13
  1. #1
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9

    Query to show differences between tables

    Hi



    I am currently updating a database of Contacts based off of different spreadsheets handled by different people. As a result I have several spreadsheets that contain Contact details of which some are the same and some different.

    I have tried creating a query using the unmatched wizard but that hasn't worked as well as i hoped in that it will only return information that differs from the primary table and i can't find a way to get it to show the original table results along side it so as to compare results however if it matches then i don't want it to show either as therefore know that entry doesn't need updating

    The problem i assume is based around the Is Null function, the one thing i thought of that might help would be an IF function command that would check each column of the two tables and if they are equal than output nothing but if they differ then output the differing results in the query.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Here is one way. Use the "Find Unmatched Query" Wizard. This will only allow you to join on one field, but it will get you started.
    After you complete it, open up your Query in Design View, and create joins for all other fields, making sure that they are the same join types as the original (so the arrow will be pointing from your Primary table to the other tables).

    If you do this right, it will return only records that differ. And you can return whatever fields from both tables that you need.

  3. #3
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9
    I have tried this approach and it keeps giving an error saying i have to create the joins in SQL.

    So i've had a play around and constructed an IIF statement that in my mind should give me my desired results but it is still failing to show the differing data from the DataToday table instead just showing blank cells, can't see where i'm going wrong with this.

    Here is the statement: I read it as If the results in DataToday are not equal to the results in DataYesterday then output the result in the DataToday table
    Code:
    Original: IIf([DataToday].[Full Name]<>[DataYesterday].[Full Name],[DataToday].[Full Name])

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    How many contact records are there? Do you have them all in different tables in Access or 1 table main table(that has the dups)? Do they all have the same exact fields and datatypes?

  5. #5
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9
    The data is in two tables that share the same formatting and layout with the same Primary Key but some of the results in the second table don't match the records in the first and the first has some records that aren't in the second

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can the primary key be recreated for all contacts or do they have to stay the same as in that key is linked in other tables already? Do all the fields have to match for it to be unique or if they have same first name, last name, birthday then they are the same? If you try to match say on address or other fields and being from different people, you will probably have different spellings for address for example, even though the contact is really the same person.

    How many records total in both tables?

  7. #7
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9
    Currently got 123 records in both tables (71 in one and 52 in the other), the primary key is not too important at this point as not used anywhere else yet and currently just trying to match on names

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    That's not many records. I would combine both tables into 1 table with new key field, sort by name and then just manually remove dups and fix any missing fields.

  9. #9
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9
    Thats fine for this one but this is really just a testing database, the actual one is going to have many more records so not really the solution i'm after

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    That's why I was asking how many records. What is the ongoing process going to be like? Is this a one time process to get all these records into 1 table of contacts, then everyone will use that Access table for contacts or will you continually have to keep importing contacts into Access?

    Is there any field that is unique to each contact other then the PK such as SSN or a license number, etc.? The problem I see with trying to automate this is with various folks free typing names, addresses, etc., many will probably not match although they are the same contact. And if say you have Ave. and Ave on 2 records, vba code will not know that they are dups.

    Can you list out your table and fields and some sample data? Others might have a good solution also.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I agree with Bulzie, a small simplified example with your expected results would be helpful here. Try to show all the different scenarios that may happen (i.e. two records that match, records that are different, records in one table not in the other). Once we see what you want to happen in each scenario, I am confident that we can come up with something that should work for you.

    Note: There are two key pieces of information that might be employed here.
    1. Join Type - If you want to show ALL the records from one table, regardless of whether or not there is a match in another, you will probably need to use a LEFT JOIN.
    2. The NZ function - If there are not the exact same number of records in each table, you will probably need to use the NZ function when comparing two fields. This tells it what to use if there is no match (it does not like to compare a Null value to anything else). See: https://www.techonthenet.com/access/...dvanced/nz.php

  12. #12
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9
    So this is what i have so far in my practice database, Data Yesterday is my original and Data Today contains some differences.

    Currently the query i have so far as Differences works well in showing the data that is in Data Yesterday but not in Data Today, however i want it to also show the entries in Data Today so i can easily note the differences and as such discern which should be correct.

    Click image for larger version. 

Name:	Differences.png 
Views:	7 
Size:	183.2 KB 
ID:	27110Click image for larger version. 

Name:	Data Yesterday.png 
Views:	8 
Size:	202.2 KB 
ID:	27111Click image for larger version. 

Name:	Data Today.png 
Views:	7 
Size:	205.9 KB 
ID:	27112

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Currently the query i have so far as Differences works well in showing the data that is in Data Yesterday but not in Data Today, however i want it to also show the entries in Data Today so i can easily note the differences and as such discern which should be correct.
    I assume that you mean that you have an Unmatched Query between the Data Yesterday table and the Data Today table (shows record in the Data Yesterday table not in the Data Today table).
    If not, please change your query to SQL View, and copy and paste the SQL code here, so we can see exactly what you are doing.

    You may need to create another Unmatched Query, going the other way, showing data in the Data Today table not found in the Data Yesterday table.

    If looking for differences on "matching" records (i.e. match on one field, and look for differences in another), you would simply join your two tables on the field you want to match on, then create expressions or criteria to look for differences. Note that if a record does not have a "match" on the field you are joining on, that record will not be returned in your query (unless you do a Left Join).
    If you need help with this part, explain in more detail exactly what fields you are matching on and which fields you are looking for differences.

    When I have two lists and am looking for differences, I typically end up with three queries like this:
    1. One to look for new records (records not in table A but in table B)
    2. One to look for old records that dropped off (records in table A but not in table B)
    3. One to look for changes in existing records (records exist both in tables A and B, but some non-matching field has changed)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-04-2016, 02:42 AM
  2. Show differences on chart
    By Skizzly in forum Queries
    Replies: 13
    Last Post: 09-30-2015, 01:02 PM
  3. Replies: 9
    Last Post: 12-03-2013, 02:33 PM
  4. Compare and show differences ...
    By raghuprabhu in forum Queries
    Replies: 5
    Last Post: 05-18-2012, 06:05 PM
  5. Compare tables and tell me differences
    By cowboy in forum Programming
    Replies: 2
    Last Post: 08-11-2010, 08:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums