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,
AMcClungAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
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.
0
 
AMcClungAuthor Commented:
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
0
 
hnasrCommented:
List down few data records and the required output.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AMcClungAuthor Commented:
With the above tables in mind, only the [Work Orders:TBL] and the [Technicians:TBL] matter.  Sample records might look like:
w/o#
 
0
 
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.  
0
 
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.
0
 
hnasrCommented:
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.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
harfangCommented:
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.

(°v°)
0
 
AMcClungAuthor Commented:
Thanks gentlemen.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.