Query with 2 LEFT JOINS to different tables not bringing back what I want

[CodeLiftSleep]

I have three tables I am working with where I need to join together various data in a query.

I have an accounts table, an Employee Averages table and a Client Table.

I have a UDF called GetCurrUser() which I use in the query, which returns the user's EmployeeId via the Environ("username") variable. This is a defined query that runs on load to get the proper accounts for the person opening the report.

In the accounts table, they are assigned accounts by the month, so for the employee I need to return ALL accounts where they have been assigned for ANY month. This part is working properly. Then I LEFT JOIN the Client table on accounts.ClientID = Client.ID and accounts.Grp# = client.Grp#. This part works fine as well.



I then additionally LEFT JOIN EmpAverages.EmpId = accounts.EmpId...and this is where it doesn't always return the right information. Sometimes it returns the right employee data and other times it returns data for a different employee...I am assuming because I don't have anything in the WHERE clause with Emp.Name = GetCurrUser() ? I tried that and it didn't return anything...I also tried to do the LEFT JOIN on Emp.Name = GetCurrUser() and it threw an error saying I couldn't do that.

Here is the full query below, I am hoping someone can help me out with getting it working properly and returning the information I need for that employee only.

SELECT t.SAM, a.ClientID, t.[Grp Name], IIf([t].[Tier Classification]="Tier 1","Tier 1","Tier 2") AS Tier, a.JanSAM, a.FebSAM, a.MarSAM, a.AprSAM, a.MaySAM, a.JunSAM, a.JulSAM, a.AugSAM, a.SepSAM, a.OctSAM, a.NovSAM, a.DecSAM, t.Segment, t.[GRP #], a.IsActive, a.JanActive, a.JanScore, a.FebActive, a.FebScore, a.MarActive, a.MarScore, a.AprActive, a.AprScore, a.MayActive, a.MayScore, a.JunActive, a.JunScore, a.JulActive, a.JulScore, a.AugActive, a.AugScore, a.SepActive, a.SepScore, a.OctActive, a.OctScore, a.NovActive, a.NovScore, a.DecActive, a.DecScore, a.YTDScore, EmpAverages.Tier1Avg, EmpAverages.Tier2Avg, EmpAverages.YTDAvg, a.EmpID, EmpAverages.EmpId
FROM (Accounts AS a LEFT JOIN UnionT AS t ON (a.ClientID = t.ID) AND (a.[GRP #] = t.[GRP #])) LEFT JOIN EmpAverages.EmpId = a.EmpId
WHERE (((a.JanSAM)=GetCurrUser())) OR (((a.FebSAM)=GetCurrUser())) OR (((a.MarSAM)=GetCurrUser())) OR (((a.AprSAM)=GetCurrUser())) OR (((a.MaySAM)=GetCurrUser())) OR (((a.JunSAM)=GetCurrUser())) OR (((a.JulSAM)=GetCurrUser())) OR (((a.AugSAM)=GetCurrUser())) OR (((a.SepSAM)=GetCurrUser())) OR (((a.OctSAM)=GetCurrUser())) OR (((a.NovSAM)=GetCurrUser())) OR (((a.DecSAM)=GetCurrUser()));

[aytee111]

Keep things as simple as possible - always. Separate these into separate queries, you say the first part works, leave that as one query and then join in the averages info.

Note: when the database opens call GetCurrUser and put it as a text box on the main form, then always refer to it rather than repeating the subroutine. Also, Access does not need the table alias's, generally it is not used. Optional however.

[CodeLiftSleep]

I'm not sure that would really help...the LEFT JOIN is still bringing back the wrong data at times...Perhaps rewrite it as a subquery?

Why wouldn't I want to use a table alias instead of typing out the full name of the table?

[aytee111]

First you are saying it wouldn't help, then you say it is still bringing back the wrong data. Did you try it?

It is user choice, but the norm is not to use them, that is all I am saying.

I am wondering about your table structure, with one record containing all the months/emp ID. It would be preferable to have those as separate records.

[CodeLiftSleep]

First you are saying it wouldn't help, then you say it is still bringing back the wrong data. Did you try it?

It is user choice, but the norm is not to use them, that is all I am saying.

I am wondering about your table structure, with one record containing all the months/emp ID. It would be preferable to have those as separate records.

It probably would make sense to do that, but unfortunately I don't have control over that, I just have to get it working with as it is currently structured...

So you are saying to create a new query, and pull the information in from the first query and then do a join on that data?

[aytee111]

Yes. The first part is working properly. Now you will have a query with all the right info, with employees. Now make a new query and join in the last table. At the very least it will be easier to troubleshoot.

[CJ_London]

cross posted here https://www.access-programmers.co.uk...d.php?t=298339

OP advised of crossposting etiquette