Shift table

[fraufreda]

hello,

I hope you can help me on this.

I am working on a shift scheduling. the problem I am facing is that I want to be able to change the number of employees per shift in table rather than modifying it in the code.

this table (or query) of the number of employees per shift which I want to create is going to be on weekly basis. In other words, every shift during the week has to have one employee but in some days in specific shifts the number of employees is going to be more than one.

I hope I have explained myself clearly. find the attached file.

I will be grateful if you can help me on this.

thank you

 

[plog]

This query should provide you with what you asked for:

SELECT ShiftDetailDate, ShiftNumber, COUNT(ShiftNumber) AS Employees
FROM tblShiftDetail
GROUP BY ShiftDetailDate, ShiftNumber

 

[fraufreda]

Many thanks for your reply. But I don't know where to insert the code so would you please help me incorporate your code into the existing one? I am just a beginner in access. I will appreciate if you can help me.

 

[plog]

Create a new query. Use tblShiftDetail as the data source. Click the Sigma in the ribbon (aka Summation symbol). In the bottom portion where you define what fields to use make the first field contain ShiftDetailDate and underneath it Group By, then ShiftNumber with Group By as well and for the 3rd field have it be ShiftNumber again but this time have Count underneath it.

That will build the query I wrote.

 

[fraufreda]

How can I add multiple conditions in docmd.runsql. I think since the current code is not updating tblshiftdetail based on tblvoidDate, multiple conditions are required within the docmd.runsql. I have tried to figure it out by searching the net but couldn't. any help??

 

[plog]

You can add no conditions in docmd--it only takes SQL. You would have to write the SQL to include any criteria.

I have no idea what you are trying to do with your tables, if you could better explain it maybe I can help. Start by explaining why you need to update a table based on another table.

 

[fraufreda]

thanks for your reply.
I don't want to assign any employees who are off or in vacation to any shifts based on the tblVoidDate.