AMcClung
asked on
Query another query and return calculated result based on whether content of field is listed in another table
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.
Thanks!
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.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
Your problem is clear, but there is one last thing I would like to know, that when you say that Technicians TBL is a historical listing, then lets have the example given below
Workorder Query results are given below:
WorkorderID Assignment Company
-------------------------- ---------- ---------- -----
101 Employe1 XYZ
102 Employe2 DEF
103 Vendor 1 ABC
Now lets assume your Technicians table have following data
WorkorderID TechniciansId TechniciansName Company DateStart DateLAstupdated
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
101 Emp1 Employe1 XYZ 10-Dec-2009 10-Dec-2009
102 Emp2 Employe2 DEF 02-JAN-2009 10-Dec-2009
102 Emp4 Employe4 DEF 10-Dec-2009 10-Dec-2009
Now looking at above data and your inputs, its very simple to say 103 is a VENDOR as its is not found in Technician table. But for 102 the problem is that it has a historical assignment to Employe2 but current assignment to Employe4. Here LastUpdatedon is the key column. In case you want to arrive at your original requirement of finding whether a workorder is assigned to a Vendor and Technician, then tehre has to be a way to find our current or VALID records from Technician table on which to search whether the assignment column in WORKORDERS query exists or not.
Else there has to be some otherway that you have to explain which gives you a list of records from technician table on which to filter data.
If you can state this, then writing the SQL is simple.
REgards,
Arpit
Your problem is clear, but there is one last thing I would like to know, that when you say that Technicians TBL is a historical listing, then lets have the example given below
Workorder Query results are given below:
WorkorderID Assignment Company
--------------------------
101 Employe1 XYZ
102 Employe2 DEF
103 Vendor 1 ABC
Now lets assume your Technicians table have following data
WorkorderID TechniciansId TechniciansName Company DateStart DateLAstupdated
--------------------------
101 Emp1 Employe1 XYZ 10-Dec-2009 10-Dec-2009
102 Emp2 Employe2 DEF 02-JAN-2009 10-Dec-2009
102 Emp4 Employe4 DEF 10-Dec-2009 10-Dec-2009
Now looking at above data and your inputs, its very simple to say 103 is a VENDOR as its is not found in Technician table. But for 102 the problem is that it has a historical assignment to Employe2 but current assignment to Employe4. Here LastUpdatedon is the key column. In case you want to arrive at your original requirement of finding whether a workorder is assigned to a Vendor and Technician, then tehre has to be a way to find our current or VALID records from Technician table on which to search whether the assignment column in WORKORDERS query exists or not.
Else there has to be some otherway that you have to explain which gives you a list of records from technician table on which to filter data.
If you can state this, then writing the SQL is simple.
REgards,
Arpit
Hi,
1. First create a query called QGetAssignment as
SELECT AssignDetails.WorkID, AssignDetails.AssignedToID
FROM AssignDetails
WHERE AssignDetails.WorkID)<5; <-- Put your condition here to fetch the necessary data from work order assignment table. If you want this to be generic, do not code any WHERE condition. Code it in your outer query as mentioned above in 26089486
2. Use the above query 26089486 and it should return you the necessary data
1. First create a query called QGetAssignment as
SELECT AssignDetails.WorkID, AssignDetails.AssignedToID
FROM AssignDetails
WHERE AssignDetails.WorkID)<5; <-- Put your condition here to fetch the necessary data from work order assignment table. If you want this to be generic, do not code any WHERE condition. Code it in your outer query as mentioned above in 26089486
2. Use the above query 26089486 and it should return you the necessary data
ASKER
Never could understand how to implement this solution. I'm awarding the points for his effort and so that I can close the question out. My problem is still unresolved.
ASKER
Can you give me a little more instruction on how to implement this solution?