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.

This is how I had the query for a long time and it was working as intended. However, additional requirements found me needing to do another LEFT JOIN and now this is where things are getting a little wonky.

Now I 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()));

 

[plog]

In the accounts table, they are assigned accounts by the month,

I really think you should fix this before you start building atop it. That's not a correct table. You shouldn't store values in field names. Instead, you should store them in records. This will require a new table. Read up on normalization (https://en.wikipedia.org/wiki/Database_normalization).

Access makes such a mess of SQL. Can you screenshot your relationship Design View--where all the tables are and post it here? Try and expand the tables so we can see all your fields.

 

[codeliftsleep]

I really think you should fix this before you start building atop it. That's not a correct table. You shouldn't store values in field names. Instead, you should store them in records. This will require a new table. Read up on normalization

Access makes such a mess of SQL. Can you screenshot your relationship Design View--where all the tables are and post it here? Try and expand the tables so we can see all your fields.

Unfortunately I don't control how the tables were built, I simply have to get this working as is...

Would a subquery be preferable to a second LEFT JOIN?

 

[CJ_London]

for the benefit of responders, cross posted here with responses

http://www.accessforums.net/showthread.php?t=70593

@code - please read this link about the etiquette of cross posting http://www.excelguru.ca/content.php?184

 

[plog]

Who does control how the tables are built? Perhaps you can ask them?

A lot of people seem to think their hands are tied and they are slaves to historical decisions beyond their control. This is often not so.

For your specific issue, I really can't wrap my mind around it with just that SQL. Can you post a screenshot of the query in design view?

 

[Pat Hartman]

I agree with all the comments about normalization. Having unnormalized tables simply makes your job harder.

However, Access does not correctly interpret SQL that includes left joins when criteria is applied to one of the right-side tables. To solve the problem, Remove the right table that has the criteria into a separate query. This query will return only rows where there is a match in the right side table.

Then go back to the original query and substitute this query for right table that had the criteria. You can left join. This will now not interfere with the other left join.

 

[CJ_London]

Access does not correctly interpret SQL that includes left joins when criteria is applied to one of the right-side tables

not sure what the OP is trying to achieve but you can modify the criteria from say

RightTable.field=somevalue

to

RightTable.field=somevalue OR RightTable.field is null

 

[plog]

The criteria is only being applied to the table in the FROM.

 

[codeliftsleep]

for the benefit of responders, cross posted here with responses


@code - please read this link about the etiquette of cross posting

My apologies, I wasn't aware of this rule. However, even if I was, there is little I could do about it since I can't post links yet. In fact I actually had to delete the links you provided to quote you.

 

[CJ_London]

not a problem, but until you can, you can always paste a link as text