Help with complicated DLookup and/or IIf expression

[julz85]

I have a table (TempAssociates) containing a list of all employees (Owner) and their ID (WWID). Using a series of left joins i created a select query that populates the hierarchy of managers for each employee. Therefore, in this select query (SupervisorHierarchy) I have columns titled "WWID", "Owner", "SL1WWID", "SL2WWID", "SL3WWID", "SL4WWID", "SL5WWID", "SL6WWID", and "SL7WWID", where SL#WWID refers to the ID of the Supervisor and the Supervisor Level # in the hierarchy. In another table (WorkCenters) I have a list of Manager IDs (ManagerWWID), Manager Names (ManagerName), and their Department Description (WorkCenter). I want to create a query that assigns each employee (Owner) a Department Description (WorkCenter) if the Manager ID (ManagerWWID) matches "SL1WWID" OR "SL2WWID" OR "SL3WWID" etc. I have attempted the following:

1. Create a select query with SupervisorHierarchy. Add fields "WWID" and "Owner". Add field "DepartmentDescription" with the expression IIf([SL1WWID]=[WorkCenters]![ManagerWWID], [WorkCenters]![WorkCenter], IIf([SL1WWID]=[WorkCenters]![ManagerWWID], [WorkCenters]![WorkCenter], "Other")). When I ran the query I got a prompt for [WorkCenter]![ManagerWWID] because Access thinks it is a parameter instead of a field in a table.

2. Create a select query with SupervisorHierarchy. Add fields "WWID" and "Owner". Add field "DepartmentDescription" with the expression DLookup("[WorkCenter], "WorkCenters"). This populates every record result with the first result in the WorkCenter table. I modified the expression to be DLookup("[WorkCenter], "WorkCenters", "[ManagerWWID] = [SL1WWID]") I got an error that Access cannot find SL1WWID. I modified the expression again to be DLookup("[WorkCenter], "WorkCenters", "[ManagerWWID] = [SupervisorHierarchy]![SL1WWID]") and I again got an error that Access cannot find SL1WWID.

3. Create a select query with SupervisorHierarchy and WorkCenters, unlinked. Add fields "WWID" and "Owner". Add field "DepartmentDescription with the expression IIf([SL1WWID]=[ManagerWWID], [WorkCenter], IIf([SL1WWID]=[ManagerWWID],[WorkCenter], "Other")). When I ran the query I got multiple instances of "WWID" and "Owner". One of the instances has the correct "DepartmentDescription" and the remainder were populated with "Other". My record count increased from ~5700 to ~18000.

4. Create a select query with SupervisorHierarchy and WorkCenters, linked on [SupervisorHierarchy]![SL1WWID] and [WorkCenters]![ManagerWWID]. Add fields "WWID" and "Owner". Add field "DepartmentDescription with the expression IIf([SL1WWID]=[ManagerWWID], [WorkCenter], IIf([SL1WWID]=[ManagerWWID],[WorkCenter], "Other")). When I ran the query I got only the "WWID" and "Owner" results that have a manager matching at SL1. I would rather not repeat this query 7 times (once for each SL #) then create a UNION ALL query.



A version of this was originally created in Excel using VLookups and works great. However, due to the size of the Excel file, it takes about 15 minutes to update each week with new employee information. Anyone have a suggestion of how best to match the ManagerWWID with the SL# so that I can get the Department Description assigned to each employee?

Thanks in advance!

[ranman256]

you shouldn't need IIF statements for queries...
joining a lookup table solves this IF THEN issue.

[ssanfu]

Welcome to the forum......

1. Create a select query with SupervisorHierarchy. Add fields "WWID" and "Owner". Add field "DepartmentDescription" with the expression IIf([SL1WWID]=[WorkCenters]![ManagerWWID], [WorkCenters]![WorkCenter], IIf([SL1WWID]=[WorkCenters]![ManagerWWID], [WorkCenters]![WorkCenter], "Other")). When I ran the query I got a prompt for [WorkCenter]![ManagerWWID] because Access thinks it is a parameter instead of a field in a table.

Cannot refer to a field in a table like that ([WorkCenters]![ManagerWWID])

---------------------------------

2. Create a select query with SupervisorHierarchy. Add fields "WWID" and "Owner". Add field "DepartmentDescription" with the expression DLookup("[WorkCenter], "WorkCenters"). This populates every record result with the first result in the WorkCenter table. I modified the expression to be DLookup("[WorkCenter], "WorkCenters", "[ManagerWWID] = [SL1WWID]") I got an error that Access cannot find SL1WWID. I modified the expression again to be DLookup("[WorkCenter], "WorkCenters", "[ManagerWWID] = [SupervisorHierarchy]![SL1WWID]") and I again got an error that Access cannot find SL1WWID.

Two things here:
1) You have

DLookup("[WorkCenter], "WorkCenters", "[ManagerWWID] =  [SL1WWID]")

There is a missing double quote after [WorkCenter] (before the first comma)
Should be

DLookup("[WorkCenter]", "WorkCenters", "[ManagerWWID] =  [SL1WWID]")

2) You have the criteria "[ManagerWWID] = [SL1WWID]". What you are saying is "Give me the workcenter field value from the WorkCenters domain (table) where the field [ManagerWWID] value is equal to [SL1WWID]".

You need to concatenate the VALUE from the column (in the query) [SL1WWID], not the column name itself.
So the DLookup would look like this

DLookup("[WorkCenter]", "WorkCenters", "[ManagerWWID] =  " & [SL1WWID])

I cannot tell what data types the fields are, so if "[ManagerWWID] is a text type field, you will need to add delimiters.

---------------------------------

3. Create a select query with SupervisorHierarchy and WorkCenters, unlinked. Add fields "WWID" and "Owner". Add field "DepartmentDescription with the expression IIf([SL1WWID]=[ManagerWWID], [WorkCenter], IIf([SL1WWID]=[ManagerWWID],[WorkCenter], "Other")). When I ran the query I got multiple instances of "WWID" and "Owner". One of the instances has the correct "DepartmentDescription" and the remainder were populated with "Other". My record count increased from ~5700 to ~18000.

The reason the record count increased is that you created a Cartesian join. Every record in SupervisorHierarchy is matched with every record in WorkCenters.

---------------------------------

4. Create a select query with SupervisorHierarchy and WorkCenters, linked on [SupervisorHierarchy]![SL1WWID] and [WorkCenters]![ManagerWWID]. Add fields "WWID" and "Owner". Add field "DepartmentDescription with the expression IIf([SL1WWID]=[ManagerWWID], [WorkCenter], IIf([SL1WWID]=[ManagerWWID],[WorkCenter], "Other")). When I ran the query I got only the "WWID" and "Owner" results that have a manager matching at SL1. I would rather not repeat this query 7 times (once for each SL #) then create a UNION ALL query.

A version of this was originally created in Excel using

This is an indication of a non-normalized table structure. I appears that someone tried to convert something that works in Excel into an Access database without doing a proper design. This is known as "Committing Spreadsheet". (Yes, it is so common a problem that it has a name)




Sorry I can't provide a better answer, but you haven't provided the field types, table structures or table relationships.