none
Access: SQL: Left Join Method RRS feed

  • Question

  • Hi Everyone:

    I have a 'Clients' Table and a 'Signs' Table. One Field 'Lagna' in the 'Clients' Table is fed through a Combo Box whose values are in the 'Signs' Table. Client:Lagna has a RELATION to Signs:ID (See Pic)

    My problem is when I use the 'Clients' Table as the Source for Word's Mail Merge the 'Lagna' field understandably return a numeric value (which is of course the ID in the 'Signs' Table).

    I want to make a Query to replace the numeric value on the 'Lagna' field of the Query with the Actual Lookup value in the 'Signs' Table. (Examples: 1 = Aries and 8 = Scorpio)

    I am aware that this could be done with a LEFT JOIN but not very clear about the SQL Syntax and where it goes into the Query while keeping everything else intact from the the 'Clients' Table.

    Any suggestions? Thanks - pd


    Thanks & Best Regards Priyantha A Dias



    • Edited by Paddy_Bear Sunday, August 6, 2017 6:24 AM Left / Right Correction
    Sunday, August 6, 2017 5:10 AM

Answers

  • You could use

    SELECT tblClients.ID, tblClients.FName, tblClients.LName, tblClients.DOB, tblClients.TOB, tblClients.POB, tblSigns.Sign AS Lagna,  tblClients.Nakatha, tblClients.EM
    FROM tblClients LEFT JOIN tblSigns ON tblClients.Lagna=tblSigns.ID


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Sunday, August 6, 2017 7:55 AM

All replies

  • You could use

    SELECT tblClients.ID, tblClients.FName, tblClients.LName, tblClients.DOB, tblClients.TOB, tblClients.POB, tblSigns.Sign AS Lagna,  tblClients.Nakatha, tblClients.EM
    FROM tblClients LEFT JOIN tblSigns ON tblClients.Lagna=tblSigns.ID


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Sunday, August 6, 2017 7:55 AM
  • Dear Hans MVP:

    Works perfect. Thank you very much for the prompt reply. I have marked your answer as 'Solved' and added 1 Vote.


    Thanks & Best Regards Priyantha A Dias

    Sunday, August 6, 2017 9:06 AM
  • Thank you. Please note that there was a typo in my reply: I wrote tblSigns,Sign (with a comma). It should have been tblSigns.Sign (with a dot). I have edited my previous reply.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 6, 2017 11:04 AM
  • Dear Hans:

    Thank you for the correction. I didn't find a problem any way. Probably I have corrected that comma after I cut  and pasted your reply. This is what I have now:

    I have added a sort on the last line. I also made the <Expr> Column 'invisible' for the datasheet view.

    Thanks once again.


    Thanks & Best Regards Priyantha A Dias

    Sunday, August 6, 2017 4:45 PM
  • Which column showed up as <Expr> ?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 6, 2017 5:59 PM
  • Dear Hans:

    Now I think I know what happened. I had not corrected the typo and got an extra column. When I 'unticked' it not to be visible I think SQL got rid of the comma and put a dot there by itself! Have a look:


    Thanks & Best Regards Priyantha A Dias

    Sunday, August 6, 2017 7:52 PM
  • Ah, OK. That explains it!

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 6, 2017 8:29 PM
  • Dear Hans:

    Sorry to bother you again. This is my latest stupidity. After amending the SQL for 'Lagna' I found that I have to amend for another Table called 'Nekaths' the same way. I added another LEFT JOIN as in the Pic. Access didn't accept it. I tried all kinds of syntax modifications to no avail. Could you please put this right. Thanks.


    Thanks & Best Regards Priyantha A Dias

    Monday, August 7, 2017 5:17 AM
  • You can use the keyword FROM only once.

    Change the FROM clause to

    FROM (tblClients LEFT JOIN tblSigns ON tblClients.Lagna=tblSigns.ID) LEFT JOIN tblNakatha ON tblClients.Nakatha=tblNakatha.ID


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 7, 2017 2:18 PM
  • My Dear Hans:

    It works! Brilliant! I wouldn't know what I would do without you! Unfortunately I have used the only vote I have for this thread... I tested the resulting Query by using it as the Source for Mail Merge. Perfect!

    What I am surprised at is this: I have been scouring the internet almost all day and nowhere it is mentioned that FROM can be used ONLY ONCE. I also have not seen any example coding with the brackets () you have used here. You are a Genius...

    I was just making a Test Database to eliminate the possibility of my tables being irrevocably corrupted and almost gave up on my work. You have saved me a ton of trouble... my heartfelt gratitude to you...


    Thanks & Best Regards Priyantha A Dias


    • Edited by Paddy_Bear Monday, August 7, 2017 4:29 PM Addition
    Monday, August 7, 2017 4:27 PM