codeliftsleep
New member
- Local time
- Today, 04:11
- Joined
- Oct 24, 2017
- Messages
- 5
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.
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.
Code:
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()));