Help me create this query | That seems impossible

[updatesvc]

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.Employee.accdb

[ranman256]

I created a 'calendar' of days, (every day of the year) tDays table
[Day], [dow]
1/1/15, Thu
1/2/15, Fri
etc thru the whole year.


Then qsDaysWorked, shows what days a carpenter is working:

SELECT Carpenters_Table.Name, Order_table.Item, Order_table.Customer_No, Order_table.Order_No, Order_table.Date_of_Order, Order_table.Days_To_Complete, Order_table.Date_of_completion, tDays.Day, tDays.DOW
FROM tDays, Carpenters_Table INNER JOIN Order_table ON Carpenters_Table.Carpenter_No = Order_table.Carpenter_No
WHERE (((tDays.Day)<=[Date_of_completion] And (tDays.Day)>=[Date_of_Order]) AND ((tDays.DOW)<>"Sat" And (tDays.DOW)<>"Sun"))
ORDER BY Order_table.Order_No, tDays.Day;


Then THIS query COUNTS the # of projects/day for a carpenter. To see if he >4

SELECT Carpenters_Table.Name, tDays.Day, Count(tDays.Day) AS CountOfDay1
FROM tDays, Carpenters_Table INNER JOIN Order_table ON Carpenters_Table.Carpenter_No = Order_table.Carpenter_No
WHERE (((tDays.Day)<=[Date_of_completion] And (tDays.Day)>=[Date_of_Order]) AND ((tDays.DOW)<>"Sat" And (tDays.DOW)<>"Sun"))
GROUP BY Carpenters_Table.Name, tDays.Day;

then this crosstab query shows the work days, (but cant show overlap)


TRANSFORM First(qsDaysWorked.Item) AS FirstOfItem
SELECT qsDaysWorked.Name
FROM qsDaysWorked
GROUP BY qsDaysWorked.Name
PIVOT qsDaysWorked.Day;

[updatesvc]

Thanks alotyou really helped me out.
But what happens when for example the next year reaches. (I created a table with all the dates for 2015)
Is there any way that you could make the databse it in away that you do not have to fill the table for days(tDays) with a very long list of dates(That someone has to keep on adding dates)


Anyway thanks for all the help you saved me alot thanks.