Results 1 to 6 of 6
  1. #1
    frozendmj is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    4

    Cool joining tables

    Hi, I have the following tables:

    Technician {TechnicianID#, Name, Surname, Address...}
    Job {JobID#, JobDate, ArrivalTime, DepartureTime, Postcode...}
    Job_Recovery {TechnicianID#, JobID#}

    the last table is an intermediate table. one job is done by two technicians. I'm writing a query to find who a particular technician (TechnicianID) worked with in the past month. The ideal result for TechnicianID=T001 would be displayed like this: (totally random values)

    Technician JobCount
    T002 2
    T003 5
    T010 3


    I got this:

    SELECT JobRecovery.TechnicianID,
    COUNT ([JobRecovery].[JobID]) AS JobCount
    FROM JobRecovery LEFT JOIN Job ON Job.JobID=JobRecovery.JobID


    WHERE Job.JobDate >= ((Date())-30)
    GROUP BY TechnicianID;

    The JobCount bit seems right and i think the job date is alright as well. I can't get the Technician part right - it displays all technicians including TechnicianID='001' and adding TechnicianID='001' after the WHERE is no good either - the table only displays technician 001.

    Could someone tell me where I am going wrong please?

  2. #2
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    You need a way to identify the jobs for a particular technician and then find the other technicians that are related. Your query only gives you the jobs a technician did...it doesn't add the additional relation. Here's one bit of sql that does what you want....you'll have to modify it for your situation. Unfortunately, you wont' be able to edit the query in the qbf.

    SELECT m.empid, j.empid, Count(j.empid) AS CountOfempid
    FROM (select distinct
    empid, jobid
    from job_recovery
    where empid = 1) m
    inner join job_recovery j
    on j.jobid = m.jobid
    and j.empid <> m.empid
    GROUP BY m.empid, j.empid;


    Another option would be to create a separate query that provides the list of jobs for a particular employee. Then create another query to relate the new query with job_recovery table.

  3. #3
    frozendmj is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    4
    thanks a lot! i did modify it a bit

    SELECT M.TechnicianID, J.TechnicianID, Count(TechnicianID) AS CountOfJobs
    FROM (select distinct
    TechnicianID, JobID
    from JobRecovery
    where M.TechnicianID = 'T001')
    INNER JOIN JobRecovery J
    ON J.JobID = M.JobID
    AND J.TechnicianID <> M.TechnicianID
    WHERE Job.JobDate >= ((Date())-30)
    GROUP BY M.TechnicianID, J.TechnicianID;

    access seems to struggle with understanding TechnicianID as it relates to more than one tables. i tried to specify [JobRecovery].[TechnicianID] and [Technician].[TechnicianID] but i got an error in the join formula. i tried numerous different combinations but i just cant get it right. could you have another look at the query above, please?

  4. #4
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    You have to be consistent with your table aliases...and it it didn't have the Job table...where the JobDate resides. Try this:

    SELECT M.TechnicianID, J.TechnicianID, Count(TechnicianID) AS CountOfJobs
    FROM (select distinct
    TechnicianID, JobID
    from JobRecovery
    where TechnicianID = 'T001') M
    INNER JOIN JobRecovery J
    ON J.JobID = M.JobID
    AND J.TechnicianID <> M.TechnicianID
    INNER JOIN Job
    ON Job.JobId = J.JobId
    WHERE Job.JobDate >= ((Date())-30)
    GROUP BY M.TechnicianID, J.TechnicianID;

  5. #5
    frozendmj is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    4
    I got a message that there's a syntax error (missing operator) in query expression ".

  6. #6
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    I don't have the database, so I can't test the queries... I would focus on your WHERE condition. Sometimes you have to learn through trial and error...

    If that doesn't work, break it up in pieces....try it....add another piece...and so on. Kind of a troubleshooting methodology.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 04:37 AM
  2. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 03:43 PM
  3. Joining or Combining Two tables
    By escuro19 in forum Queries
    Replies: 2
    Last Post: 02-16-2010, 03:55 PM
  4. Joining Unioins output to tables? Help Needed
    By techexpressinc in forum Queries
    Replies: 4
    Last Post: 06-24-2009, 05:05 PM
  5. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums