Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2015

    Lightbulb Unanswered: Employee job status(Free or not free)

    I am making a database for work employees.The employees are given jobs that last between | DateofBegining| and |Date of completion| In the job table.Other attributes in the Table are |Doneby(Employee Id)| , |JobID| etc. I want to to know which employees are free on a particular date . How do i go about this. ImagePlease help.

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 32
    you have the emp table , tEmps
    and the job table , tJobs
    you need a form, with a text box, txtStartDate

    your query will join tEmps and tJobs on EmpID
    select *.tEmps from tEmps , tJobs where forms!frmFind!txtStartDate not between tJobs.StartDAte and tJobs.EndDate

    to look for employees NOT currently working between their job using the date on the form.

  3. #3
    Join Date
    Aug 2015

    Question Query help

    What if an employee can have a maximum of 4 Jobs/Task/Orders to manage at the same time. The 4 Jobs can have different time durations overlaping other job periods for the same employee.
    (My jobs last more than a day, use the first data base without a [time]attribute)
    I have included an attachment of my database.

    1.Can you help write a query to display employees(Carpenter_No) that have jobs less than 4 and also display the number of their current jobs.
    NB:Their jobs overlap in time jobStartDate is the same as |Date_of Order| and jobEndDate is the same as |Date_of_completion| in orders table.

    2.Also a query to display employee|(Carpenter_no)| who have jobs more than 4 and also display the number of their current jobs.
    More illustrations found in the database example in attachment.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    what you could do is use a sub query
    create a query which finds all employees with tasks allocated during that period. then JOIN that query in another query using a left join


    select EmployeeID from mytable
    where mydate beween DateofBegining and Dateofcompletion
    ..replace employeeid, mytable, dateofbeginnign,dateofcompletion mydate with the correct names in your table
    employeeid ..the employee identifier
    mytable..the name of your table identifying what employees are tasked
    dateofbeginnign, dateofcompletion
    mydate..the date you want to search for if its the current day then you could use DATE() in place

    the using a LEFT JOIN, join that query to the employees table. a left JOIN says return all rows that match from the leftmost tabel, and only those rows that match in the rightmost table
    select e.employeeid from employees as e
    left join qFindTaskedEmployees as q
      on e.employeeid = q.employeeid
    where isnull(q.employeeid)
    the trick is that in a left join if there are not no matches in the right table then the columns in that tabel will return NULL. so addign a where clause that accepts only employee int he query that are NULL.
    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