Compare two tables and record calculated value in query
Please help,
I have two tables in a MS Access 2007 DB. One table contains work orders and all relevant information to them, and a second table contains a list of service technicians. I need to be able query the work orders table and segregate work orders assigned to a technician from those assigned to vendors. Ideally, what I would like to have is a query with a calculated field that query's the work orders table and then compares the [assignment] field in the work order table to the list of technicians in the Technicians table ([tech] field). If the [assignement] field in the W/O Table contains an assignment name (i.e. technicians name) listed in the Technicians table I would like for the calculated field in the query to return a value of "1". If not, then a value of "0". I could then query this query to pull out the work orders assigned to technicians or vis versa as needed.
Please let me know if you need more information.
Thanks,
I have two tables in a MS Access 2007 DB. One table contains work orders and all relevant information to them, and a second table contains a list of service technicians. I need to be able query the work orders table and segregate work orders assigned to a technician from those assigned to vendors. Ideally, what I would like to have is a query with a calculated field that query's the work orders table and then compares the [assignment] field in the work order table to the list of technicians in the Technicians table ([tech] field). If the [assignement] field in the W/O Table contains an assignment name (i.e. technicians name) listed in the Technicians table I would like for the calculated field in the query to return a value of "1". If not, then a value of "0". I could then query this query to pull out the work orders assigned to technicians or vis versa as needed.
Please let me know if you need more information.
Thanks,
ASKER
Helen,
Below are the relationships... see attached. It's not just a matter of being able to pull the tech name out of the technicians table though.... I'm looking to return a value in a calculated field of the query based on the name in the assignment field of the work order.... so all of the action is taking place around the work orders table. I'm just hoping to be able to reference the technicians table to determine what value should be returned for the calculated field in the query.
Relationships.JPG
Below are the relationships... see attached. It's not just a matter of being able to pull the tech name out of the technicians table though.... I'm looking to return a value in a calculated field of the query based on the name in the assignment field of the work order.... so all of the action is taking place around the work orders table. I'm just hoping to be able to reference the technicians table to determine what value should be returned for the calculated field in the query.
Relationships.JPG
List down few data records and the required output.
ASKER
With the above tables in mind, only the [Work Orders:TBL] and the [Technicians:TBL] matter. Sample records might look like:
w/o#
w/o#
ASKER
Sorry... sample records might look like
Work order records contained in [Work Orders:TBL]
W/O# Priority .... Address ..... Assignment.... etc (all w/o fields will be populated)
123 P1 123 Main St Tech1Name
234 P3 555 My Ave. Vendor3
432 P1 99 1st St Tech3Name
I want to query all of the data/fields for every record in the [Work Orders:TBL] AND add a calculated field that returns either a "1" or "0", based on who the work order is assigned to. If the work order is assigned to a Technician (technician names are stored in the [Technician:TBL] then I want the calculated field to return a "1"..... if not, then I want it to return a "0".... thus I need to be able to compare the [assignment] field in the [work orders:TBL] to the entire list of technician names in the [Technicians] table (if you look at the screen shot above, the technicians names are stored in the [Tech] field of the [Technicians] Table). This will then differientiate the work orders assigned to a technician from those assigned to a vendor, by 1's and 0's.
Work order records contained in [Work Orders:TBL]
W/O# Priority .... Address ..... Assignment.... etc (all w/o fields will be populated)
123 P1 123 Main St Tech1Name
234 P3 555 My Ave. Vendor3
432 P1 99 1st St Tech3Name
I want to query all of the data/fields for every record in the [Work Orders:TBL] AND add a calculated field that returns either a "1" or "0", based on who the work order is assigned to. If the work order is assigned to a Technician (technician names are stored in the [Technician:TBL] then I want the calculated field to return a "1"..... if not, then I want it to return a "0".... thus I need to be able to compare the [assignment] field in the [work orders:TBL] to the entire list of technician names in the [Technicians] table (if you look at the screen shot above, the technicians names are stored in the [Tech] field of the [Technicians] Table). This will then differientiate the work orders assigned to a technician from those assigned to a vendor, by 1's and 0's.
ASKER
What i don't know how to do is compare the contents of the [assignment] field to the entire list of [tech] names in the technicians table..... think VLookUp type of action in Excel.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks gentlemen.
Another possibility would be to have a field in the Work Orders table that would be set to "Technician" or "Vendor", and then you could filter by that field.
For more specific information, please post your database.