Results 1 to 15 of 15
  1. #1
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9

    Quick Question about my Left Join query for Unmatched records...

    Hello, I’m very desperate here…can anyone pelase help me. I’m using a Query to find unmatched records between two tables. I’m using 4 common fields between the two tables, for matching. It’s been working well, except, for this scenario: I’ve found that if table 1 has 3 rows of data where those 4 matching fields MATCH only 1 row of data in table 2, the query considers those 3 rows from table 1 to be reconciled. I need the query to only use the 4 fields ONCE when comparing the rows to table 2.

  2. #2
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    I'm not fully understanding your question, but here is my guess at what you are asking.
    Table_1 has:
    Field1 - (Age?)
    Field2 - (Gender?)
    Field3 - (First Name?)
    Field4 - (Last Name?)
    OtherTable1Fields

    Table_2 has the same matching 4 fields.

    Both Table_1 and Table_2 could have the same records repeating, and in your case Table_1 repeats 3 times, but you only want it to return one match instead of 3.

    Instead of joining Table_1 to Table_2, create a Summary Query and Group By Field1-4 and then join the Summary Query to table_2.

    You could have the same problem with Table_2 also repeating records.

  3. #3
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    Thank you for your reply Peter. I did try a Summary Query with a larger data set. I’ve paired it down while I try to figure this out. I think that’s a great suggestion. So to repeat what you’re saying…If the fields 1-4 from Table 1 repeat 3 times, and those same matching fields 1-4 in Table 2 only appear once, then I should end up with an unmatched summary count from Table 1 (because only 1 of the 3 matching fields (rows) were found in Table 2. Right? J

  4. #4
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    clarification...(because only "1 of the 3 matching field GROUPS.." (rows) were found in Table 2.

  5. #5
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    Table 1 Table 2
    Account Item number Qty Order date Account Item number Qty Order date
    Abc123 Item123 24 5/21/18 Abc123 Item123 24 5/21/18
    Abc123 Item123 24 5/21/18 Ghi123 Item789 4 5/19/18
    Abc123 Item123 24 5/21/18 Ghi123 Item789 4 5/19/18
    Def123 Item456 10 5/22/18 Match Def123 Item456 10 5/22/18

    I’m trying to reconcile data between two tables by matching 4 fields (exactly). The problem is, like in this example, if there are multiple occurrences of the 4 fields in Table 1, then it only takes one row in Table 2 where the 4 fields match, and the query thinks it’s matched all 3 rows from Table 1, when really, again in this example, I need 3 rows to match in both tables.

  6. #6
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Are you saying that in your example only the first row and the last row match?

    From your perspective, why doesn't Table_1-Row_2 match with Table_2-Row_1?

    If you want every row to match with every row you need some criteria to order the data (in both tables), and then add a sequential ranking. Join on the sequential rank and add filters [Table_1].[Account] <> [Table_2].[Account] OR [Table_1].[Item Number] <> [Table_2].[Item Number] OR etc.

    However, I would be very cautious about doing that. If you are trying to ensure that one table is a copy of another you are probably better off re-copying the data.
    If you are looking for differences between the two tables then you probably want to compare all three instances of Account ABC123 Table_1 with ABC123 in Table_2.

  7. #7
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    I’m sorry, it’s so odd to communicate through forums. I like your question though! To answer your question, Table_1-Row_2 shouldn’t match Table_2-Row_1 because that item (ABC123) was entered 3 times on 5/21/18 with the exact same quantity. BUT item (ABC123) only flowed over to Table 2 ONCE, because there was an interface problem (or something). So that’s why I do this reconciliation. I’m trying to make sure that each item from Table 1 flowed to Table 2.

    Again, it works great unless the fields 1-4 are repeated in Table 1, because right now, the query only needs to see those fields 1-4 in Table 2 ONCE, and it things all of the lines from Table 1 have flowed.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you are saying that you want any record from table 1 to appear in table 2 (i.e. if there are 4 of what look like identical records in 1 you want them in 2 anyway) then if your tables really look like what you've shown, I believe that your design is the problem. There is no unique field in 1 that you could copy over to 2 as a foreign key, thereby allowing a query to append to 2.

    Regretfully, I've read your posts more than once and can't really decipher what you're trying to do. If I've stated it correctly, then I have to wonder why you would want to do that anyway. It indicates there are bigger problems if data entry is failing. If on the other hand, you only want one record from 1 regardless of the number of identical records, plus one record from 2, then in the Totals query ("summary" from post 2) you would use the TOP 1 predicate - if there are other fields in 2 that you also need. If the fields are the same, then a UNION query should work. I would not try to add a sequential numbering field and make that work.

    Maybe you should step back and explain what the business problem is (e.g "I have source data and I'm trying to ensure table 2 has all of it") rather than what doesn't work. We seem to know more about what hasn't worked to solve a problem as opposed to knowing what the problem is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    Hello, I’m hoping someone might be of some assistance with my Access Database dilemma. BACKGROUD: My database is using two tables, Table 1 is from one system and Table 2 is from a second system. I’ve been using a query to find records from Table 1 that do not have a match in Table 2. These unmatched items indicate that data entered into the first system (Table 1) failed to populate in the second system (Table 2). I’m using four fields that exist in both Tables (we’ll call them fields 1-4) to find unmatched records. I’m using LEFT JOIN with the “Is Null” criteria in the Table 2 fields. PROBLEM: It works very well, as long as the data in Table 1 (fields 1-4) are not repeated, which does happen when the same account number orders the same item numbers, with the same quantity, on the same date. Each of those orders end up on separate lines (rows) in Table 1 (and will flow that way to Table 2). So, here’s what I’m looking for a solution to. You can see in my example that Table 1 has 3 duplicate records (ID 1, 2 , & 3), and Table 2 does show that record (ID 1) but only once. So, when my query looks at Table 2 to find matches for Table 1 data, it finds that (Table 1, ID 1, 2, & 3) = (Table 2, ID 1). But really only 1 of those 3 identical records in Table 1 interfaced over to Table 2. But my query does not return any of those Table 1 items (ID 1, 2, or 3) as UNMATCHED. The query is using that 1 record in Table 2 (ID 1) as a MATCH for all three Table 1 (ID 1, 2, & 3) items. So, it thinks we’re good, that we’ve reconciled. But we haven’t. I want my query to tell me it has 2 items in Table 1 that were not found in Table 2.


    Table 1 Table 2
    ID Account Item number Qty Order date ID Account Item number Qty Order date
    1 Abc123 Item123 24 5/21/18 1 Abc123 Item123 24 5/21/18
    2 Abc123 Item123 24 5/21/18
    3 Abc123 Item123 24 5/21/18

  10. #10
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    I don't usually work with the SQL, but here is that logic to represent my set up:

    SELECT [Table 1].Account, [Table 1].[Item number], [Table 1].Qty, [Table 1].[Order date], [Table 2].Account, [Table 2].[Item number], [Table 2].Qty, [Table 2].[Order date]
    FROM [Table 1] LEFT JOIN [Table 2] ON ([Table 1].[Order date] = [Table 2].[Order date]) AND ([Table 1].Qty = [Table 2].Qty) AND ([Table 1].[Item number] = [Table 2].[Item number]) AND ([Table 1].Account = [Table 2].Account)
    WHERE ((([Table 2].Account) Is Null) AND (([Table 2].[Item number]) Is Null) AND (([Table 2].Qty) Is Null) AND (([Table 2].[Order date]) Is Null));

  11. #11
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    I had tried using subtotal functions, and Peter M suggestedthat too. My data sets are so large, soI prepared two 100 row test files to use for Table 1 and Table 2representations. I’ve still got a-waysto go to have something like that fully integrated with the data-pulls from mytwo systems, but I’ll keep plugging away at that. None the less, I thought someone might have atrick or tip to tell Table 1 that it can only match (fields 1-4) to Table 2(Fields 1-4) ONCE. And if Table 1 hasadditional duplicate records, it needs to match them to another subsequent setof (fields 1-4) in Table 2.

  12. #12
    ronmaltase is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2018
    Posts
    9
    None the less, I thought someone might have a trick or tipto tell Table 1 that it can only match a row of (fields 1-4) to a row in Table2 (Fields 1-4) ONCE. And if Table 1 hasadditional duplicate records in another row, it needs to match them to another subsequentrow of (fields 1-4) in Table 2.

  13. #13
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Knowing that you want to reconcile Table_1 with Table_2 changes things. You aren't necessarily looking for a match, you want to know if something does not match which record(s) does not match.
    It might take more than one step.

    [This is a bit of PS but belongs at the beginning, if possible add a field that can serve as a control total to Table 1 and Table 2.]

    Problem: Is every record in Table_1 also in Table_2?
    For Table_1:
    Group By Fields1,2,3,4
    Sum every other numeric field
    Count whatever is left (count at least 1 field, even if it is one already used) as Fld_Check_Count

    Repeat this query for Table_2:
    Group By Fields 1,2,3,4
    Sum the numberics
    Count whatever is left as Fld_Check_Count

    If Fields 1,2,3,4 are unique in Table_2 then the maximum value of Fld_Check_Count should be 1, if not then there is a problem in Table_2.

    Join the two tables on Fields_1,2,3,4,Fld_Check_Count.
    Left Join Table_1 and Show the nulls - this will identify the Fields_1,2,3,4 that are not replicated the same number of times.
    Left Join Table_2 and show the nulls (but for the other table)

    Problem: Is every record in Table_1 the same as Table_2?
    If every record is replicated, then compare the summarized fields (where possible). Join Table_1 with Table_2 on Fields 1,2,3,4, and Fld_Check_Count and
    filter where the SUM fields are different - this will identify where the reconciliation is inaccurate (the value in the Table_1 record is not the same in Table_2). Technically you can still have errors if values numerically cancel, if that is likely you can resolve that with sum of squares.

    Even if you are working with millions of records these should not take very long to run provided the Fields 1,2,3,4 are indexed in both Table_1 and Table_2.

    To my knowledge you can't force a database table to make one match, and then move on to make another match with the exact same criteria without forcing the data to be sorted first and then imposing an additional constraint (like row count). Just because the table records look ordered to us does not mean they are permanently ordered in the background. (I have dealt with a lot of real-life problems where the programmers assumed the data tables would be read in a static order and they did not explicitly sort the data table they were reading. Unfortunately this doesn't become apparent during testing and it is only when inconsistent results appear that you notice there is a problem.)

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You should be able to solve this by adding a field to both tables that makes each record unique, such as order I'd, and reversing the join to get all from table1. You problem is that multiple table1 records are identical. Surely there is something that will correct this? Order id? Date stamp with time?

  15. #15
    Ricks41at is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    4
    oops, wrong place

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

Similar Threads

  1. Left Join with Crosstab query
    By FL0XN0X in forum Access
    Replies: 13
    Last Post: 03-05-2018, 04:22 PM
  2. Replies: 3
    Last Post: 06-30-2014, 12:53 AM
  3. Replies: 1
    Last Post: 12-08-2011, 01:52 PM
  4. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  5. Quick question about deleting records
    By pinecrest515 in forum Queries
    Replies: 3
    Last Post: 12-14-2010, 10:58 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