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_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 = '')

[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_details.SOEID = '' and WHERE tbl_employee_details.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_details.SOEID, tbl_employee_details.FirstName, tbl_employee_details.Surname, tbl_employee_details.StartDate, tbl_hardware_requests.HardwareType, tbl_hardware_requests.HardwareRequestNumber, tbl_software_requests.ApplicationName, tbl_software_requests.SoftwareRequestNumber, tbl_voice_requests.VoiceRequestType, tbl_voice_requests.VoiceRequestNumber
FROM ((tbl_employee_details INNER JOIN tbl_hardware_requests ON tbl_employee_details.SOEID=tbl_hardware_requests.S OEID) LEFT JOIN tbl_software_requests ON tbl_employee_details.SOEID=tbl_software_requests.S OEID) LEFT JOIN tbl_voice_requests ON tbl_employee_details.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]

@code green
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)