Hello,
I need help to represent finished or future shifts, for each group of employees daily in each month (1 to 30 or 31 depending on month) in a crosstab query and then report
Employee groups are A, B, C, D, E
Each Group has to be represented with a regular shift each day of month in the reports eg 06:00 - 14:00 with a periodicity
eg: Group A shift today works from 14:00 - 20:00, same day Group D has day off etc
How should one
a) calculate the periodicity of shift changing as days go by, for each group working shifts
For example
Today Group A, shift 1 06:00 – 14:00
Group B, shift 2 14:00 – 22:00
And b) represent it (the goal of it all) in a crosstab report
Like this example:
so far i have accomplished to have my employee groups in a select query named QrPlano
and afterwards a crosstab query QrPlanocross
which works but lacks to what i need .. a) future shift changing between days (as a label ) depending on group of employees
b) days of month if above is achieved, not to be forced to 31 because not all months count to 31 days
Excuse me if not explaining something well
I need help to represent finished or future shifts, for each group of employees daily in each month (1 to 30 or 31 depending on month) in a crosstab query and then report
Employee groups are A, B, C, D, E
Each Group has to be represented with a regular shift each day of month in the reports eg 06:00 - 14:00 with a periodicity
eg: Group A shift today works from 14:00 - 20:00, same day Group D has day off etc
How should one
a) calculate the periodicity of shift changing as days go by, for each group working shifts
For example
Today Group A, shift 1 06:00 – 14:00
Group B, shift 2 14:00 – 22:00
And b) represent it (the goal of it all) in a crosstab report
Like this example:
| Group A | | |||||
1/10/2022 06:00 - 14:00 | 2/10/2022 14:00 - 22:00 | 3/10/2022 22:00-06:00 | 4/10/2022 Rest | 5/10/2022 Day off | 30 or 31/10/2022 Some calculated shift | |
| EmployeeA | Duty1 | Duty2 | Duty3 | No duty | Day off | Some duty |
| EmployeeB | Duty1 | Duty2 | Duty3 | No duty |
| Group B | | |||||
1/10/2022 14:00 – 22:00 | 2/10/2022 22:00 - 24:00 | 3/10/2022 rest | 4/10/2022 Day off | 5/10/2022 06:00-14:00 | 30 or 31/10/2022 Some calculated shift | |
| EmployeeA | Duty2 | Duty2 | No duty | Day Off | Duty1 | Some duty |
| EmployeeB | Duty2 | Duty2 | No duty | Day Off | Duty1 |
so far i have accomplished to have my employee groups in a select query named QrPlano
Code:
PARAMETERS [Forms]![ypovolesfrm]![cboVardia] Long, [Forms]![ypovolesfrm]![ZMONTH] Long, [Forms]![ypovolesfrm]![ZYEAR] Long;
SELECT ypiresiestbl.Ypiresiadate, vardiesypaltbl.ypallilosID, ypiresiestbl.eidosypiresiasID, vardiesypaltbl.vardiaID, ypiresiestbl.orarioID
FROM (ypaliloitbl INNER JOIN vardiesypaltbl ON ypaliloitbl.ypallilosID = vardiesypaltbl.ypallilosID) INNER JOIN ypiresiestbl ON ypaliloitbl.ypallilosID = ypiresiestbl.ypallilosID
WHERE (((vardiesypaltbl.vardiaID)=[Forms]![ypovolesfrm]![cboVardia]) AND ((Format([Ypiresiadate],"m"))=[Forms]![ypovolesfrm]![ZMONTH]) AND ((Format([Ypiresiadate],"yyyy"))=[Forms]![ypovolesfrm]![ZYEAR]))
ORDER BY ypiresiestbl.Ypiresiadate;and afterwards a crosstab query QrPlanocross
Code:
TRANSFORM Max(QrPlano.eidosypiresiasID) AS MaxOfeidosypiresiasID
SELECT QrPlano.ypallilosID
FROM QrPlano
GROUP BY QrPlano.ypallilosID
PIVOT Format([Ypiresiadate],"d") In ("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31");which works but lacks to what i need .. a) future shift changing between days (as a label ) depending on group of employees
b) days of month if above is achieved, not to be forced to 31 because not all months count to 31 days
Excuse me if not explaining something well
Attachments
-
group table.JPG -
serviceday table.JPG
Last edited:
