Access Query - Populating a field based on other fields in the record..

So I am trying to populate "Yes" or "No" in the Supervisor Level field based on if the Employee Code record is also a Supervisor Code somewhere in the table data.  For instance results could look like this:

Employee Code      Supervisor Code      Supervisor Level
123      130      Yes
124      131      Yes
125      131      Yes
126      125      No
127      124      No
128      124      No
129      123      No
130      123      Yes
131      123      Yes

Can this be done within the query as an Expression?  Thanks.  - Tom
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
Yes, you could generate the Yes or No based on IIF statement.

The field in the Query would be something along the lines of

Supervisor Level : IIF(DLookup("[Employee Code]","Employee Table", "[Employee Code] = [Supervisor Code]") is Not Null, "Yes", "No")
tomfarrarAuthor Commented:
My apology, I am trying to populate the field for each record in a table I have imported (at point of import), and not a query.  Not sure it makes a difference, but...

I have identified the Supervisor Level field as "Calculated" in the table design and tried to build a function to do what you have suggested using an Iff statement.  But the result of the DLookup needs to be compared against the Employee Code to see if there is a match.  Said another way, is the Employee also a Supervisor.

I think you understand that, but I am not seeing how that comparison is made with:
Supervisor Level = IIF(DLookup([Supervisor Code],[Employee Table]) is Not Null, "Yes", "No")

Does that make sense, Richard?  Thanks.  - Tom
Richard DanekeTrainerCommented:
Tom, I reworked my DLookup.  But the plan is this- if the dlookup result is NOT NULL, then, the supervisor code is a valid employee number.   If result is Null, then it is not a supervisor.

So, the IFF statement uses an expression that returns true or false, the value when true, the value when false.  
So, the first statement is:
  DLookup("[Employee Code]","Employee Table", "[Employee Code] = [Supervisor Code]") is Not Null
Then, if this is true - "YES" - it is a supervisor
and,  if this is false - 'No' - it is not a supervisor.
It should work on the calculated field on import as well.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

tomfarrarAuthor Commented:
Thanks, Richard, I understand the IIF function and what you said makes sense to me.  I have not in the past added a table field as a "Calculated" Data Type where it takes me to the build function window.  But when I do, it is not liking the IIF statement and says I cannot use this expression in a calculated column.  Is it possible I have written the expression wrong or is there trouble with my table design?  Thanks.
Richard DanekeTrainerCommented:
If the IFF statement cannot be used in a calculated field you still have two options.

1. Use a query with the IIF statement as a new field and use the query where the table was intended.
2. Rename your table and create a maketable query that will add the Yes/No field.  Here you would need update and append queries to maintain the data later.
I don't have enough information to say one way or the other.  Where is the information coming from.  You say you are loading a table but what are you loading it from?  A spreadsheet?  An external file?

You can try this method.  Basically, you have to Join the table to itself on EmployeeCode to SupervisorCode and any EmployeeCode returned is a supervisor.  The query to do that is:

Select tblA.EmployeeCode As IsSupervisor
From tblA Inner Join tblA as tblA_1 on tblA.EmployeeCode = tblA_1.SupervisorCode
Group By tbl.EmployeeCode

tblA in this query is your source table which could be a linked spreadsheet or text file.

You would join to this query in your append query using a left join  and if IsSupervisor is not null, then your SupervisorLevel field should be set to True, otherwise False.
If you are trying to define a Calculated field, this won't work.  You can only reference columns of the current row.

My solution assumed you were storing the information in your employee table as a permanent column.  Usually, the supervisor flag is set on an employee record to indicate that he is allowed to be used as a supervisor.  It sounded like you were importing data from a different source and this flag was not included so you were attempting to create it.  Technically it is not a violation of any normal forms since a supervisor does not need to have any supervised employees to be a supervisor so having a flag is necessary.  This isn't something you can calculate on the fly.
tomfarrarAuthor Commented:
So to answer some of the questions, let's start with the original data again:

Employee Code      Supervisor Code      Supervisor Level
123      130      Yes
124      131      Yes
125      131      Yes
126      125      No
127      124      No
128      124      No
129      123      No
130      123      Yes
131      123      Yes

The first two columns (Employee Code and Supervisor Code) are imported into the same table (Employee Table) from a spreadsheet.  These two fields (Employee Code and Supervisor Code) are separate columns for each Employee Table record.  

What I am hoping to do is to add a new column for each Employee Record that identifies if an employee is also a supervisor (of other employees).  I was hoping to create the "Yes" (is a supervisor) or "No" (is not a supervisor) when the spreadsheet data is imported.  

That is why I added the Supervisor Level field in the Employee Table design view as a "Calculated" field in advance of the importing the spreadsheet data.  Using an IIF statement (liked that described by Richard) seemed promising, but perhaps won't work.  

I am attaching a spreadsheet example and a database where it would be imported into with the Employee Code and Supervisor Code fields populated, and needing the Supervisor Level field determined.
Did you try my suggestion?  Link to the spreadsheet.  Then create a query that joins it to itself to get the IsSupervisor flag.  Then join that query using a left join to the linked spreadsheet to do your append query.
Jeffrey CoachmanMIS LiasonCommented:
Give Pat's suggestion a try an get back to her..

What you seem to be doing there is akin to creating a employee lookup based on a unique listing of Employees in the "Orders" table.  In this case you should simply create the lookup from the Employees table.
Here is another way of looking at this...
Instead if creating an "expression" to determine the Supervisor Level, ...why not store it in a table?:
For example:
eID (PK)
eIsSupervisor (Yes/No)

Another approach used in the old Northwind Access sample database was to use a "Reports To" field.
This was a field in the employees table that listed the employee ID of the person they reported to.
With this design, you could use various simple queries (one involving a Self-Join) to get:
All Employees
Employees without Supervisors (Top level Employees)
Employees with Supervisors
See the attached sample database...

Finally, consider if a supervisor can have a supervisor....
With multiple levels of Supervisors reporting to other supervisors, ...things can get tricky.

I think this is what he is trying to do.  The OP is simply starting with data that comes from somewhere else and doesn't contain the supervisor indicator so it has to be built.
tomfarrarAuthor Commented:
Thanks for the additional comments...

The data is coming from a spreadsheet that has been downloaded from a system and saved in a folder.  The employee and the employee's supervisor are separate columns on each record (row) of the spreadsheet.  I can add a field using a If(VLookUp ..., "Yes", "No") to the spreadsheet after download and it works fine in identifying if an employee is also a supervisor.

I was hoping to avoid adding the field to the downloaded spreadsheet and let Access populate the field by adding a field with the first query that would determine the Yes/No supervisor status of each employee.  But I also didn't want to add additional queries that might confuse someone who will need to take over database maintenance at some point.

What Pat has suggested may do exactly what is needed, but being a visual person, I have to get my head around the words.  And I will as soon as I have some time to think it through...  Or I will just do it in Excel on the downloaded spreadsheet.  Thanks for your help.  - Tom
Although you are creating two queries, you only run the append query.  the append query will run the other one.  Access treats tables and queries interchangeably for most purposes so joining to a query is essentially the same as joining to a table.  Just remember that queries don't store data.  Tables store data so when you are updating/adding/deleting, you are only affecting the underlying tables.

Wen you create databases that others will need to operate - and even for your own sanity, it is best to create forms with buttons that run the steps so you don't have to remember to do them individually.
tomfarrarAuthor Commented:
Hi Pat - Can you tell me where the flaw is in my Append query?  The error is "Operation must include an updateable query "....

I have attached the linked spreadsheet and Access DB.  Thanks for your help.  - Tom
Your append query is attempting to append to EMPLOYEE TABLE which is the linked spreadsheet.  You need to create an Access table to hold the data and then append to that.

I changed it to a Make-table query just so you could see it work.
SELECT [EMPLOYEE TABLE].[Employee Code], [EMPLOYEE TABLE].[Supervisor Code], IIf(IsNull([Is Supervisor Qry]![Supervisor Code]),"NO","YES") AS [Supervisor Level] INTO [tblEMPLOYEE TABLE]
FROM [EMPLOYEE TABLE] LEFT JOIN [Is Supervisor Qry] ON [EMPLOYEE TABLE].[Employee Code] = [Is Supervisor Qry].[Supervisor Code];

Open in new window

This will make a new table in the database.
If that works for you, leave it as a make table query.  Otherwise, create the table you want to end up with and change the query back to an append query but include all the column names, not just the yes/no field.

If you have an existing table that you want to update, that is more of a problem because the query you are using to collect the data is not updateable for two reasons.
1. It joins to an Excel table which is not updateable
2. It joins to a totals query that aggregates data and aggregations are not updateable.

The solution would be a temporary make table to save the data from the IsSupervisorQuery.  You would then join that table to the existing table and update it using an update query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfarrarAuthor Commented:
Thanks, Pat.  It does make (obvious) sense I can't append to a linked spreadsheet, which I haven't done much of.  In the past I have imported the spreadsheet to Access.  But I do like the option of linking the spreadsheet as it takes out one step (the import) for the final users.  It also appears to allow the spreadsheet fields to change (if need be) and Access seems to be able to adapt.  I am sure this can also be precarious too.

But for right now, where we are proto-typing what the initial-final data may look like, the linked spreadsheet seems to provide more flexibility than a hard-and-fast table in Access.  Though I have been able to make the append update as you've suggested, I guess for now I will determine supervisor "yes/no" in Excel.  Unless you have other ideas....  Thanks for your help.  - Tom
If you want to see the data in Excel, you will need to run a macro in Excel to update the Supervisor column.

Yes, Access will see the new columns of the spreadsheet and it may even resolve column name changes and reordering.  However, if you have any queries based on the original spreadsheet, they will break if names change or columns are deleted and they will not include new columns.  But the biggest problem with working with linked spreadsheets, is sharing.  Access does not play well with others and it will not share the linked spreadsheet.  That is the reason I always import the spreadsheet if I'm going to need it for more than a couple of minutes before closing the database.
tomfarrarAuthor Commented:
Thanks, Pat.  I understand the issues with the queries breaking and I feel this can be handled.  I would like to know more about the "not sharing" the linked spreadsheet.  I don't think this is a problem as the spreadsheet it is linked to is data downloaded from another system that is saved to a common drive.  There should be no editing/changing of the spreadsheet once saved.  There really shouldn't be anyone even opening the spreadsheet once it is saved to the common drive.  Does this get rid of the sharing problem?  Thanks.  - Tom
Sharing is only an issue if someone or some other application wants to open the file when Access has it open or vice versa.  Sounds like it is a non-issue for you.
tomfarrarAuthor Commented:
Thanks, All.   Thanks Pat for walking me through the issues here.  - Tom
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.