Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Is it possible to return name of an employee only once if they have done several jobs

    I would be most grateful for any urgent assistance please. I have two tables. One called tblEmployee which has the following fields:

    EmployeeID
    FirstName
    Surname
    Mobile


    I then have another table called tblBookings which among other fields has the following fields:

    BookingID
    EmployeeID
    DateOfJob


    On this database we have over 100 employees who have not done any jobs for us and I want to create a query give me a list of all those Employees who have not done any jobs for us (which means the date of job is blank or null).

    And I am not just able to retrive this information. When i join the two tables in a query using the Select Query Wizard, it just gives me a list of all employees who have done jobs for us and it shows their names several times because they have done several jobs for us. I want to see their names once and only those names that have not done any job for us.

    Any help would be extremely appreciated. My boss has given me a deadline until tomorrow and I am just not able to do this.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dunno what version of access you are using but there is usually a wizard that will do what you want. IIRC its called the unmatched query wizard
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2006
    Posts
    386
    I am using access 2002, by the way this is the same issue as my previous thread.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Using the Find Unmatched Query Wizard you would end up with:
    Code:
    SELECT tblEmployee.EmployeeID, tblEmployee.FirstName, tblEmployee.Surname, tblEmployee.Mobile
    FROM tblEmployee LEFT JOIN tblBookings ON tblEmployee.EmployeeID = tblBookings.EmployeeID
    WHERE (((tblBookings.EmployeeID) Is Null));
    By the way, this is not what you were asking in your previous thread, quite the contrary actually:
    Quote Originally Posted by Emal View Post
    ... so that the Query gives us a list of all those employees that actually done jobs on certain dates.
    while now you're asking for:
    Quote Originally Posted by Emal View Post
    ... I want to create a query give me a list of all those Employees who have not done any jobs for us
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you tried the unmatched query wizard?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •