Results 1 to 15 of 15

Need Help

  1. #1
    schro224 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    7

    Need Help


    I am trying to create a Query that combines 3 tables of data and excludes the like information from all 3 tables.

    I have 194517 rows of raw data in the first table. 136 in a second table and 54928 in the 3rd that both have duplicate information from the first.

    I've tried using an IFF statement but I can't seem to get it right as well as a yes/no but the yes/no is applying to the entire table raw data not just the duplicates.

    Any guidance would be really appreciated!

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    So, are you saying that the three tables and are structured the same, as there is duplicate information between the tables?
    And you are trying to combine it into one single listing, with all duplicates removed?

    You can use a UNION query to do this.
    See:
    https://www.techonthenet.com/sql/union.php
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,240
    When you say like, do all 3 tables have the same fields so this would be records where all fields have the same data? Or you just talking about a key field in each table? If the former, you could just append all the records from the 3 tables into 1 table, then use Totals/Groupby on all the fields in a Make table to create the final table.

  4. #4
    schro224 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    7
    The tables do not have the same structure. Does that matter? They all contain 1 same field but have different fields as well.

  5. #5
    schro224 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    7
    The problem with an union is that you still get the data set one time. if table 2 has the information in table 1 I don't want the result at all.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,240
    Might be good to give sample of your fields and data in the tables, then what you want it to look like.

  7. #7
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    I agree with Bulzie. I really do not understand what you are after, so an example might be the best way of showing us what you are trying to do.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  8. #8
    schro224 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    7
    Click image for larger version. 

Name:	picture.png 
Views:	11 
Size:	52.3 KB 
ID:	28016 The field I want to show is NB-Xtra without NB-Duplicates and NB

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    I don't think you have clearly communicated to us just what it is that you are after.
    We are not so much looking for table structures, as we are of sample data from each table, and what your expected output from those samples should look like.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  10. #10
    schro224 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    7
    Click image for larger version. 

Name:	table 1.png 
Views:	11 
Size:	167.9 KB 
ID:	28018Click image for larger version. 

Name:	table2.png 
Views:	11 
Size:	162.1 KB 
ID:	28019Click image for larger version. 

Name:	table3.jpg 
Views:	11 
Size:	174.1 KB 
ID:	28020 the goal is to show the information in EritreaXtra that does not have the same NB as the other 2 tables

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    the goal is to show the information in EritreaXtra that does not have the same NB as the other 2 tables
    What if the NB appears in exactly one of the other two tables? Do you want to show it then?
    Or only show it if it does not in exist in BOTH of the other tables?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  12. #12
    schro224 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    7
    If it exists in either table 2 or table 3 I do not want to see it in table 1

  13. #13
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    OK. I created a simple example of three tables named Table1, Table2, and Table3, each with field name "NB".
    And I want to return all NB values from Table1 where the NB value is not found in either Table2 or Table3.

    The query would look like this:
    Code:
    SELECT Table1.NB
    FROM (Table1 LEFT JOIN Table2 ON Table1.NB = Table2.NB) LEFT JOIN Table3 ON Table1.NB = Table3.NB
    WHERE (((Table2.NB) Is Null) AND ((Table3.NB) Is Null));
    Basically, you get this by creating an Unmatched Query from Table1 to Table2 (using the Unmatched Query Wizard), and then edit the query, adding Table3, and creating the same join from Table1 to Table3 and adding the same criteria.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  14. #14
    schro224 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    7
    I received an error: Cannot join on Memo, OLE or hyperlink Object (table1.nb=table2.nb)

    I checked all 3 tables NB is listed as long text

  15. #15
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,832
    In newer versions on Access, the change the name of the "Memo" field type to "Long Text". You cannot create Joins with these type of fields.
    The values you posted in your examples look pretty short. Why was "Long Text" used? Try changing them all to "Short Text".
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

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

Tags for this Thread

Posting Permissions

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