[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

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.

2 Solutions
Helen FeddemaCommented:
It sounds to me as if you need a one-to-many or many-to-many relationship between the Work Orders table and the Techniclans table.  If a work order can have only one technician assigned, the one-to-many relationship is appropriate; if multiple technicians could be assigned, a many-to-many relationship.  In either case, once the appropriate relationship is set up, you can easily pull the records assigned to a technician.
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.
AMcClungAuthor Commented:
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.

List down few data records and the required output.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

AMcClungAuthor Commented:
With the above tables in mind, only the [Work Orders:TBL] and the [Technicians:TBL] matter.  Sample records might look like:
AMcClungAuthor Commented:
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.  
AMcClungAuthor Commented:
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.
Build your query that includes [Work Orders:TBL], [Technicians], and [Sites]
Selct the fields you want.
Add this calculated field TechOrVend: Abs( [Work Orders:TBL].[Assignment] = [Technicians].[Tech])

For more help, if issue not resolved, attach a sample database.
Something like this?

  Exists(Select True From Technicians Where Tech = Assignment) As IsT
FROM [WorkOrders:TBL]

If the technician must also be assigned to the same Site ID (following your table structure) then perhaps

  Exists(Select True From Technicians T
    Where T.Tech = O.Assignment And T.[Site ID] = O.[Site ID]
  ) As IsT
FROM [WorkOrders:TBL] O

Note that any spelling mistake in field "assignment" breaks the link. If you can still change the table structure, I strongly advise you to revise it.

AMcClungAuthor Commented:
Thanks gentlemen.  

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now