Access: SQL: Left Join Method

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

Answer 1

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)

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

Which column showed up as <Expr> ?

---

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

Answer 6

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

Answer 7

Ah, OK. That explains it!

---

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

Answer 8

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

Answer 9

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)

Answer 10

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