Left Joins in access

[Daveyj]

Hi Guys,

I'm having a few difficulties writing a left join query in access, below is what i've done in SQL but having a few problems getting the same result from access , any help is much appreciated.


SELECT tbl_employee_de tails.SOEID, tbl_employee_de tails.FirstName , tbl_employee_de tails.Surname, tbl_requests.Re questID,
tbl_Software_ty pes.SoftwareNam e, tbl_hardware_ty pes.HardwareTyp es

FROM tbl_requests INNER JOIN

tbl_employee_de tails ON tbl_requests.SO EID = tbl_employee_de tails.SOEID LEFT JOIN

tbl_software_re quests ON tbl_requests.Re questID = tbl_software_re quests.RequestI D LEFT JOIN
tbl_Software_ty pes ON tbl_software_re quests.Applicat ionName = tbl_Software_ty pes.Application TypeID LEFT JOIN
tbl_hardware_re quests ON tbl_requests.Re questID = tbl_hardware_re quests.RequestI D LEFT JOIN
tbl_hardware_ty pes ON tbl_hardware_re quests.Hardware Type = tbl_hardware_ty pes.HardwareTyp eID


WHERE (tbl_employee_d etails.SOEID = '')

[code green]

Try using brackets.
Not sure what the rules are but Access likes brackets

[Daveyj]

thanks for the response, but i really haven't a clue where to start,

any advice is greatly appreciated

[Daveyj]

when i do the above i get the missing operator message - please help i'm pulling my hair out

[patjones]

What does the message say, exactly? Does this SQL work in any other environments?

Pat

[gershwyn]

Try this:

SELECT tbl_employee_details.SOEID,
       tbl_employee_details.FirstName,
       tbl_employee_details.Surname,
       tbl_requests.RequestID,
       tbl_software_types.SoftwareName,
       tbl_hardware_types.HardwareTypes
FROM (((((tbl_requests INNER JOIN tbl_employee_details ON tbl_requests.SOEID  = tbl_employee_details.SOEID)
LEFT JOIN tbl_software_requests ON tbl_requests.RequestID = tbl_software_requests.RequestID)
LEFT JOIN tbl_Software_types ON tbl_software_requests.ApplicationName = tbl_Software_types.ApplicationTypeID)
LEFT JOIN tbl_hardware_requests ON tbl_requests.RequestID = tbl_hardware_requests.RequestID)
LEFT JOIN tbl_hardware_types ON tbl_hardware_requests.HardwareType = tbl_hardware_types.HardwareTypeID)
WHERE (tbl_employee_details.SOEID = '')

Note that there is a parenthesis at the end of each join and 5 before the first INNER JOIN.

I've always found this "feature" of Access annoying. Especially since when I tried to run your original query as written, it said the error was somewhere in the middle of the word 'detail'.

[Daveyj]

my initial code worked when i imported the access db into SQL -

Gershwn's code worked , but brings back no results.

[gershwyn]

What results, if any, do you get when you remove the where clause?

[patjones]

Also bear in mind that there could be a difference between saying WHERE tbl_employee_de tails.SOEID = '' and WHERE tbl_employee_de tails.SOEID IS NULL. One is a zero-length string and the other is just, well, null. I have found that they both work in some circumstances, but not in others.

Pat

[Daveyj]

removed as double entry

[Daveyj]

i've used gershwyn post as an example and wrote the below query but it brings back duplicate results. any ideas?

SELECT Distinct tbl_employee_de tails.SOEID, tbl_employee_de tails.FirstName , tbl_employee_de tails.Surname, tbl_employee_de tails.StartDate , tbl_hardware_re quests.Hardware Type, tbl_hardware_re quests.Hardware RequestNumber, tbl_software_re quests.Applicat ionName, tbl_software_re quests.Software RequestNumber, tbl_voice_reque sts.VoiceReques tType, tbl_voice_reque sts.VoiceReques tNumber
FROM ((tbl_employee_ details INNER JOIN tbl_hardware_re quests ON tbl_employee_de tails.SOEID=tbl _hardware_reque sts.S OEID) LEFT JOIN tbl_software_re quests ON tbl_employee_de tails.SOEID=tbl _software_reque sts.S OEID) LEFT JOIN tbl_voice_reque sts ON tbl_employee_de tails.SOEID=tbl _voice_requests .SOEI D
WHERE (((tbl_employee _details.SOEID) =[Enter SOEID]));

[code green]

Duplicates are caused by a JOIN on a table that has more than one entry relating to the field that was joined.
There of course is nothing wrong with this as this is correct DB design.
You need to isolate a duplicate and decide what you want to filter out.

[Daveyj]

Duplicates are caused by a JOIN on a table that has more than one entry relating to the field that was joined.
There of course is nothing wrong with this as this is correct DB design.
You need to isolate a duplicate and decide what you want to filter out.

what is the best way to do that?

[code green]

The way I do it is look at the results.
select two or three employees that have a problem.
Then add a temporary addition to the WHERE clause

AND employee_id IN(id1,id2,id3)