Vlookup in a querry?

[Kamolga]

Hi,

I have a table of employees (TblEmployees). I use their ID key to link to whom they supervise

EmployeeIDName SupervisorIDSupervisorName
1 John 2 Peter
2 Peter
3 Kim 1 John

In my querry (ActiveEmployees), instead of SupervisorID, I would like to have the green SupervisorName column. I don't get how to create a relationship within a table, so I tried something like

expression:dlookup([TblEmployees]![Name],[TblEmployees]![EmployeeID],[ActiveEmployees]![SupervisorID])

but got an error.

Any idea?

 

[stumac]

You would bring the table in twice and alias it - something like:

SELECT Emp.EmployeeID, Emp.[COLOR=#ff0000]Name[/COLOR], Emp.SupervisorID, Sup.[COLOR=#ff0000]Name[/COLOR] AS Supervisor
FROM TblEmployees AS Emp LEFT JOIN TblEmployees AS Sup ON Emp.SupervisorID = Sup.ID;

I have highlighted Name in red as it is a reserved word in Access and shouldn't be used as field name.

FYI - it can be done (inefficiently) with DLookup:

Supervisor: DLookUp("name","TblEmployees","EmployeeID = " & [SupervisorID])

You would need to put in something to handle errors with this though.

 

[Kamolga]

Thank you stumac.

So after hours and hours of search I never succeeded in getting select from left join working but well the Dlookup (in about 3 minutes). I have had an Access Essential Training, I master Excel and VBA, understand what it does when I read it, kept trying to understand where and how I am supposed to input it but without luck.
To come back to my goal, it was to have a supervisor name to assign to active employees (=still working in company) rather than working with ID. My constrain to work with same ID is the way Viso can use this data to build organigrams automatically.

My solution for now was to go for a split of the tables. 1 is EmployeeID/LastName/FirstName and 2 is EmployeeID/SuprvisorID. From my querry that filter on active employee (and add a FullName field), I have a form with a combo which lists active EmployeeID (not showing) and Fullname (what user sees in combo) and which can assign the EmployeeID as SupervisorID to the table 2 with macro -> I guess it is not very clear but it works and I hope is quite a clean solution (no dlookup)

 

[stumac]

Hi Kamolga - not 100% sure how your current design is working - sounds like you may be duplicating data which is usually considered bad design - for example if a surname of a supervisor was top change would you need to update it in 2 places?

I maybe wasn't clear with my first post - I posted SQL which isn't VBA. In you create a query using the query design grid, on the ribbon under design tab the first button should be View and will default to 'Design View' if you pull down the drop down and select SQL view this is where you would paste this query.

If you have further issues with queries people will often ask you to paste the SQL code which access automatically creates for queries created using the design view - this is a good way for people to help troubleshoot any issues you have.

 

[Kamolga]

Hi Kamolga - not 100% sure how your current design is working - sounds like you may be duplicating data which is usually considered bad design - for example if a surname of a supervisor was top change would you need to update it in 2 places?

All data is in specifique table and really unique. The only column that is close to duplication is a querry column of FullName that combines LastName and FirstName of a table. I would normaly work with a combo of 2 columns but since Visio (organigram) works with FullName to attach photos automatically, I have it in the querry and use it for combo.
If somebody had a new supervisor, we woud have to change it once on the form through a dropdown list of full name and it would change the SupervisorId of table 2 (no writing of any name manually or by code).
If Miss Jones was becoming Miss Stewards, then changing her name in Employee Table would adapt everywhere (including combobox) and since the SupervisorID that shees has for her employees would still be her Employee ID, there would not be any other change. I really apply the principles of 'only pure unique data in table', formulae and calculation in querries at 100% (I think it frees space, improves speed and if I have to come back to it in 10 years). I make it difficult to myself to work only with ID and dropdowns but that helps making it 'idiot proof'...and if one person enters once an information or links two informations, nobody else will have to do it at any time.

In you create a query using the query design grid, on the ribbon under design tab the first button should be View and will default to 'Design View' if you pull down the drop down and select SQL view this is where you would paste this query.

That is where! I played with alias, source and field Thanks!
My querry is like this now:

<now()) and="" ((isnull(tcontract!enddate))="True))" or="" (((tcontract.enddate)=""> SELECT tEmployees.EmployeeID, tEmployees.LastName, tEmployees.FirstName, [LastName] & " " & [FirstName] AS FullName, tBusinessUnit.BusinessUnit, tDepartment.Department, tPosition.Position, tContract.EntryDateFROM (tBE_CP RIGHT JOIN tPersonalData ON tBE_CP.CodePostal = tPersonalData.[Postal Code]) RIGHT JOIN (tHierarchy RIGHT JOIN (tLevel RIGHT JOIN (tDepartment RIGHT JOIN (tBusinessUnit RIGHT JOIN ((tEmployees LEFT JOIN tContract ON tEmployees.EmployeeID = tContract.EmployeeID) LEFT JOIN tPosition ON tEmployees.EmployeeID = tPosition.EmployeeID) ON tBusinessUnit.BusinessUnitID = tPosition.BusinessUnitID) ON tDepartment.DepartmentID = tPosition.DepartmentID) ON tLevel.LevelID = tPosition.LevelID) ON tHierarchy.EmployeeID = tEmployees.EmployeeID) ON tPersonalData.EmployeeID = tEmployees.EmployeeID
GROUP BY tEmployees.EmployeeID, tEmployees.LastName, tEmployees.FirstName, [LastName] & " " & [FirstName], tBusinessUnit.BusinessUnit, tDepartment.Department, tPosition.Position, tContract.EntryDate, tPosition.DepartmentID, tContract.EndDate, tHierarchy.SupervisorID
HAVING (((tContract.EntryDate)<Now()) And ((IsNull(tContract!EndDate))=True)) Or (((tContract.EndDate)>Now()));

</now())>