Compare two tables and record calculated value in query

Posted on 2010-01-05
Last Modified: 2012-05-08
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.

Question by:AMcClung
    LVL 31

    Expert Comment

    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.

    Author Comment

    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.

    LVL 30

    Expert Comment

    List down few data records and the required output.

    Author Comment

    With the above tables in mind, only the [Work Orders:TBL] and the [Technicians:TBL] matter.  Sample records might look like:

    Author Comment

    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.  

    Author Comment

    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.
    LVL 30

    Accepted Solution

    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.
    LVL 58

    Assisted Solution

    Something like this?

    SELECT *,
      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

    SELECT *,
      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.


    Author Closing Comment

    Thanks gentlemen.  

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now