Results 1 to 13 of 13
  1. #1
    BH3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7

    Left Join using 2 tables does not return all results

    Hi All,



    I have a very simple problem. I want to join table a with table b twice, but return all the records from table a.

    The problem. table a has a list of systems, lets say 100. Table b list those systems and a number of types of certificates. I want two in particular, "Z1" and "Z2", both of which have a date against them I need. Now, I can do the left Join and get all the records against Z1, but get nothing but errors when trying to do the second join. Can anyone provide some assistance?

    Here is my example that works using the access query builder but it does not return all the records. I understand I need to use the AND statement against the On, but how do you do it a second time?


    Code:
    SELECT u_AllSubs.System, u_AllSubs.SubSystem, u_AllSubs.SubSystemDescription, ul_ZenCerts.[Date Accepted by Client PMT Rep], ul_ZenCerts_1.[Date Accepted by Client PMT Rep]
    FROM (u_AllSubs LEFT JOIN ul_ZenCerts ON u_AllSubs.SubSystem = ul_ZenCerts.Subsystem) 
    LEFT JOIN ul_ZenCerts AS ul_ZenCerts_1 ON u_AllSubs.SubSystem = ul_ZenCerts_1.Subsystem
    WHERE (((ul_ZenCerts.[Certificate Template]) Like "*Z85*") AND ((ul_ZenCerts_1.[Certificate Template]) Like "*Z86*"));
    As I mentioned, I can get it to work one time using the code below.

    Code:
    SELECT u_AllSubs.SubSystem, ul_ZenCerts.[Date Accepted by Client PMT Rep]
    FROM (u_AllSubs LEFT JOIN ul_ZenCerts ON ((u_AllSubs.SubSystem = ul_ZenCerts.Subsystem) AND ((ul_ZenCerts.[Certificate Template]) Like "*Z85*")));
    So does anyone have any advice or even an answer as to how I can achieve all the records from u_Allsubs and the associated info from Certs?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I don't see how including ul_ZenCerts in the query twice and linking on same fields accomplishes anything. I am surprised you get any records returned. Exactly what is the rule for this search?

    Do you want all records that have either Z85 OR Z86? How can any record have both - which is what the AND operator requires.

    Provide example of raw data and desired output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    BH3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by June7 View Post
    I don't see how including ul_ZenCerts in the query twice and linking on same fields accomplishes anything. I am surprised you get any records returned. Exactly what is the rule for this search?

    Do you want all records that have either Z85 OR Z86? How can any record have both - which is what the AND operator requires.

    Provide example of raw data and desired output.
    Because the ul_ZenCerts has a list of all the systems and a number of certificates against them. ie


    Example of u_AllSubs
    Sub1
    Sub2
    Sub3

    Example of ul_ZenCerts
    Sub1 Z85 15/05/2015
    Sub1 Z124 04/04/2015
    Sub1 Z86 20/05/2015
    Sub2 Z85 15/07/2015
    Sub2 Z124 04/03/2015
    Sub2 Z86 20/07/2015

    Desired result
    Sub Z85 date Z86 date
    ---------------------------------------
    Sub1 15/05/2015 20/05/2015
    Sub2 15/07/2015 20/07/2015
    Sub3

    Look, I may be doing the entirely wrong thing here, so any nudge in the right direction will be appreciated. this is just the way I logically put it together, but being an absolute nube, I would follow any advice rather than continue to smash my head into the table at 4.30 in the morning...

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    That output looks a lot like a CROSSTAB query. Only instead of arithmetic (Sum, Avg, etc) use First or Last aggregate function to pull the date for each cert. And if you want to show only the Z85 and Z86 records, apply filter criteria with OR operator. Filtering CROSSTAB is different from other queries. Review: http://allenbrowne.com/ser-67.html#Param

    Assuming every sub has at least one record for Z85 or Z86 in ul_ZenCerts, the join to u_AllSubs is not necessary.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    BH3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by June7 View Post
    That output looks a lot like a CROSSTAB query. Only instead of arithmetic (Sum, Avg, etc) use First or Last aggregate function to pull the date for each cert. And if you want to show only the Z85 and Z86 records, apply filter criteria with OR operator. Filtering CROSSTAB is different from other queries. Review: http://allenbrowne.com/ser-67.html#Param

    Assuming every sub has at least one record for Z85 or Z86 in ul_ZenCerts, the join to u_AllSubs is not necessary.
    Unfortunately not. The u_Allsubs table has records that do not have a certificate yet. I will look into the Crosstab query. Thanks.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Here is a technique to emulate CROSSTAB so you don't have to deal with the parameter difficulties of CROSSTAB http://datapigtechnologies.com/flashfiles/crosstab.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    BH3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by June7 View Post
    Here is a technique to emulate CROSSTAB so you don't have to deal with the parameter difficulties of CROSSTAB http://datapigtechnologies.com/flashfiles/crosstab.html
    June7, I know you are trying to help, but the crosstab query does not return all the records from the u_AllSubs table and the second video you posted (which was awesome and taught me something I needed to know) brings in more than one version of each u)Allsubs.... I am going around the bend here. It cannot be this hard!

    It appears I was on the right track at the beginning and I just need to modify my original query....somehow.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Either approach should get you the correct output.

    No, I don't think your original approach will accomplish.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    BH3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    June7,

    Thanks mate! Attached is a version of the database. I have made the query I am trying to achieve, but as you can see, it only brings in 2603 instead of the 2680 rows of the allsubs table....
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    So the query named "This is the output I want to see" is not actually giving you what you want?

    Here is the emulated CROSSTAB:

    SELECT u_AllSubs.SubSystem, Max(IIf([Certificate Template] Like "*Z85*",[Date Accepted by Client PMT Rep],Null)) AS Z85, Max(IIf([Certificate Template] Like "*Z86*",[Date Accepted by Client PMT Rep],Null)) AS Z86
    FROM ul_ZenCerts RIGHT JOIN u_AllSubs ON ul_ZenCerts.Subsystem = u_AllSubs.SubSystem
    GROUP BY u_AllSubs.SubSystem;

    Turns out Max was the appropriate aggregate function.

    Seems to be some records in ul_ZenCerts without matching SubSystem in u_AllSubs.

    Your dates are formatted for non-U.S. (i.e., non-Access standard), review http://allenbrowne.com/ser-36.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    BH3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    No, that was how I want to see it and you have replicated that, but you will notice that your crosstab query only brings in 921 of the required 2680 records from allSubs. Mine only brings in 2603.

    The objective is to list every record from allsubs and get the dates of the certificates froom the ZenCerts table.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I edited my post while you were reviewing and changed the query to remove the HAVING clause. It returns 2680 records. Review again.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    BH3 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    7
    June7,

    You sir, are a genius!! Thank you so much. I shall endeavour to review and understand your query so as not to bother you in the future! Many thanks from myself, my head and my table.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-30-2014, 12:53 AM
  2. Inner Join works but not Left Join
    By kwooten in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 05:09 PM
  3. Count with Left Join on four tables
    By SheikhMusa in forum Queries
    Replies: 3
    Last Post: 04-09-2012, 11:15 AM
  4. inner, left or right join ?
    By toqilula in forum Access
    Replies: 0
    Last Post: 04-11-2011, 12:20 AM
  5. Replies: 3
    Last Post: 02-02-2011, 01:00 PM

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
  •  
Other Forums: Microsoft Office Forums