troubleshooting Question

Query another query and return calculated result based on whether content of field is listed in another table

Avatar of AMcClung
AMcClungFlag for United States of America asked on
Microsoft OfficeDatabasesSQL
5 Comments1 Solution229 ViewsLast Modified:
Please help,

I have an Access Database with a table for "work orders" and another for "technicians".  In each work order record there is a field recording who that work order is assigned to.  In that field could be a vendor name or a tech name.  I need to be able to segregate the vendor work orders from the tech work orders.  My company has 70 +/- techncians though and probably hundreds of vendors, so it's not just a matter of entering their names in the criteria field of the query (the character limit will be exceeded).  
Is it possible to have the query look at the [assignment] field in the [Work Orders:Query] and then if the assigned to's name matches one of the tech names listed in the [technicans:TBL] return the text "Tech" in a calculated field (if the assigned to's name doesn't match one of the names in the [Technicians:TBL] I'd like the calculated field to return "Vendor").

Sidenote:  The [Technicians:TBL] is a listing of all the sites a work order could be generated at with the name of the tech assigned to each site.  The tech assignments though at our sites have recently changed on a large scale (think realignment).  The work orders in the database are of course, historical.  Thus, the name in the assigned to field of a given work order record (assuming it's a tech) may not match the name of the tech now assigned to that location in the [Technicians:TBL].... therefore, the solution to this problem is not to simply query the "tech name field" out of the [Technicians:TBL] along with all of the [work order:TBL] data and then compare the [Work Order:TBL]![assignment] field to the [Tech] field within the query .... you could end up getting a false negative if you did so --- i.e. the work order will be looking for the old tech's name and when it doesn't find him assigned to that location (since the tech assignment has changed) the query would return a "Vendor" value in the calculated field, and it shouldn't have.  I know that was a lot to digest, so please forgive me if you have to go back to the beginning and re-read the question!

If you do need further clarification though please let me know.  I'll be closely watching for responses.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros