Change weekly fields to monthly

[RE_]

Hi everyone,
attached is an extremely simplified version of the database that I’m currently using to track employees’ forecasts.
In the table there are fields called plans_week1-2-3 etc which refer to weeks (the data are hours).
In the report, I created a formula to convert the plans_week1-2-3 etc to the real weekending dates. All good if I need to run a report by week but problem is that I would now need to show months instead of weeks and sum the weekly data for each specific month.
I can’t just sum the first 4 weeks + 4 more + etc because this trick wouldn’t even work if I run the report in the first week of the month (not all months have 4 weeks).
I can easily do this in excel but I would really need some help to achieve so with an access’s report (preferably without VBA).
Thankyou so much in advance to the kind soul that can help me with this!

[June7]

Summarizing by month would be simple if data was normalized. A UNION query can rearrange data into a normalized structure. However, UNION is limited to 50 SELECT lines. You have 53 week fields - 3 too many for UNION.

As is, just add the fields. So if weeks 1 through 5 are January then plans_week1 + plans_week2 + plans_week3 + plans_week4 + plans_week5 As Jan
then if weeks 6 through 9 are February ...

[RE_]

Summarizing by month would be simple if data was normalized. A UNION query can rearrange data into a normalized structure. However, UNION is limited to 50 SELECT lines. You have 53 week fields - 3 too many for UNION.

As is, just add the fields. So if weeks 1 through 5 are January then plans_week1 + plans_week2 + plans_week3 + plans_week4 + plans_week5 As Jan
then if weeks 6 through 9 are February ...

Hi June7,

Thank you for your reply.

Unfortunately I can't just sum the fields because plans_week1 actually doesn't refer to the first week of the year but to the current one (weekending 25-03-2022). The data get pushed back manually every week which basically means that what is currently under plans_week2, next week will go under plans_week1 and so on. Hope it makes sense.

Could you please advise a bit more regarding the UNION query (I'm not quite familiar with it). I'm more than happy to exclude the last 3 fields.

Thank you very much again for your time. Appreciate it.

[June7]

So the 12 calculated fields would have to be rebuilt every week.

UNION can only be created in SQLView of query builder. Here is example of UNION:

SELECT emp_code, Discipline, Project, plans_week1 AS Data, Date()+7-Weekday(Date()+1) AS Dte, 1 AS Wk FROM tbl_staffing_plans
UNION SELECT emp_code, Discipline, Project, plans_week2, Date()+7-Weekday(Date()+1)+7, 2 FROM tbl_staffing_plans
...
UNION SELECT emp_code, Discipline, Project, plans_week50,Date()+7-Weekday(Date()+1)+336, 50 FROM tbl_staffing_plans;

To get the last 3 weeks, try building another UNION query for the last 4 weeks then reference that query in the 50th line of the first UNION. That line would be like:
UNION SELECT emp_code, Discipline, Project, Data, Dte, Wk FROM Query2;

Now review http://fmsinc.com/MicrosoftAccess/qu...ort/index.html

[ArviLaanemets]

Add a Calendary table into your database. I myself use a daily calendary table, which contains all dates until some reasonable future date, which additionally contains fields for week (in format yyyyww), for month (in format yyyymm), and also some other useful fields like weekday number, id for workday/weekend/holiday, number of normative working hours, etc. (you can also create a simpler one specially for your DB, which determines to which month a week belongs.) The table is universal for use with any application - you simply join it into your queries, and use it to group your data.

Another way is to use some general rule. E.g. for countries where week definition is based on ISO, the week belongs to month where it's Thursday belongs. But to calculate it you must have date of some weekday in this week, or number of week and year, and a formula for such calculation (and such formula will be different for different countries - depending on week definition in country).

[ssanfu]

Re: Normalization

This looks like you imported a spreadsheet from Excel. Access and Excel are two different animals.
Excel spreadsheets are short and wide
Access tables are Tall and narrow.
This happens so much it has its own name: Committing Spreadsheet.

I see at least 4 tables (to start with)

tblEmployees
------------------
EmpID_PK Autonumber
EmpFirstName Text
EmpSurame Text
EmpCode Text
--- Other Emp data

tblProjects
--------------
ProjID_PK Autonumber
ProjNumber Text
ProjName Text
ProjStartDate DateTime


tblDisciplines
----------------------
DisciplineID_PK Autonumber
DisciplineName Text

tbl_staffing_plans
---------------------
StaffPlanID_PK Autonumber
EmpID_FK Long
DisciplineID_FK Long
ProjID_FK Long
EndingWeekDate DateTime
WorkHours Double

[RE_]

Hi ssanfu,

You're absolutely right. What I have attached is only a simplified version of the database. The original one has already the tables you advised which are structured more or less in the same way. Unfortunately what you're suggesting regarding the structure of the staffing plan table won't work for me though because the employees are not spending the same amount of hours every single week on a project. Current week = 3 hours on A project, 5 hours on B project, etc. Next week = 6 hours on A project, 3 hours on B project and so on.

Hi June7,
First of all I will thank you forever because in the link you posted I learnt the use of the IN function in a crosstab (which is going to get rid of a really annoying problem in my original database).
However, and I'm sure this is related to my inexperience, I'm struggling to understand how your solution could "transform" the data from a weekly format to monthly. I understand that with a UNION query I'm able to associate plans_week1 to the current week but how can I eventually group the information together in order to produce a monthly report?

Hi ArviLaanemets,
Weeks ago I thought about having a table structured in the following way:

Plans_week1 25/03/2022 March
Plans_week2 1/04/2022 April
Plans_week3 8/04/2022 April
Plans_week4 15/04/2022 April
Plans_week5 22/04/2022 April
Plans_week6 29/04/2022 April
Plans_week7 6/05/2022 May
Plans_week8 13/05/2022 May
Plans_week9 20/05/2022 May

but this would require to re-adjust the info every week and it wouldn't really work for me. Could you please help me to understand if I'm right or wrong?

Thank you very much all again for your support. I really really appreciate it.

[June7]

Use the UNION normalized dataset as source for dynamic CROSSTAB to summarize by month.

You show dates as dd/mm/yyyy. Access expects mm/dd/yyyy. Review http://allenbrowne.com/ser-36.html

[RE_]

Use the UNION normalized dataset as source for dynamic CROSSTAB to summarize by month.

You show dates as dd/mm/yyyy. Access expects mm/dd/yyyy. Review http://allenbrowne.com/ser-36.html

I have to admit that I have reached my current limit with Access.

I created the UNION query as you suggested but I'm lost with the crosstab and the date format. Could you please advise what am I doing wrong?

[June7]

The CROSSTAB works after removing the 50th week line from UNION.

Now follow the tutorial for the CROSSTAB and report design. The CROSSTAB date calculation would be Format([Dte], "yyyy-mm")

[RE_]

The CROSSTAB works after removing the 50th week line from UNION.

Now follow the tutorial for the CROSSTAB and report design. The CROSSTAB date calculation would be Format([Dte], "yyyy-mm")

Hi June7,

That is great, I still need to build the report but I can see that the crosstab works now. Attached is the new example in case someone needs it in the future.

I guess I have only one last question: I added a new employee in the example3 database and I allocated him 40 hours per week. If I run the crosstab the result changes according to the number of weeks in a specific month (160 or 200). is there a sort of IFF statement that I can use to lock the max amount of hours to 160? Something like IFF(data>160,160,data). I know I'm abusing your patience

Thank you very much, I wish a great karma to all of you and especially to you June7.

[June7]

Correction about the date calculation. When you finish studying the tutorial, you will find that the dynamic report CROSSTAB does not use that YYYY-MM calculation.

Can put that conditional hours calculation in textboxes of report.

[ArviLaanemets]

Hi ArviLaanemets,
Weeks ago I thought about having a table structured in the following way:

Plans_week1 25/03/2022 March
Plans_week2 1/04/2022 April
Plans_week3 8/04/2022 April
Plans_week4 15/04/2022 April
Plans_week5 22/04/2022 April
Plans_week6 29/04/2022 April
Plans_week7 6/05/2022 May
Plans_week8 13/05/2022 May
Plans_week9 20/05/2022 May

but this would require to re-adjust the info every week and it wouldn't really work for me. Could you please help me to understand if I'm right or wrong?

Attached is an example how it was meant.

As 1st step, I converted your tbl_staffing_plans to more conventional structure, using queries qStaffing1 and qStaffing2 to convert old data, and qInsertPlans1 and qInsertPlans2 respectively to insert converted data into tblStaffingPlans (I needed 2 pairs of queries, as 53 UNION's in query was too much!). I also added a field FYW (the financial year week number - as from your data it looked like your week numbering started from 4th week of March).

Then I created a Calendary (tblCalendary) table, and a query (qAppendToCalendary) to fill it (on every run, the query doubles the rows in calendary table. A limitation is, the calendary table must start with Monday of 1st week of year!)

And as last, I created queries qStaffingPlansMonthly and qStaffingPlansWeekly, which you can use in reports.

[RE_]

Correction about the date calculation. When you finish studying the tutorial, you will find that the dynamic report CROSSTAB does not use that YYYY-MM calculation.

Can put that conditional hours calculation in textboxes of report.

Hi June7,

Apologies for the late reply but I have been sick lately.

Attached is a copy of the database that shows the final monthly report with the IIF function that prevents to show more than 160 hours per month.

I have also created the additional union query in order to include the fields that couldn't be included in the first one. It seems to work fine and I just thought to attach in case someone will need something similar in the future (also if you see something that doesn't look ok, please let me know ;-)

Hi ArviLaanemets,

Thank you very much for your big effort on making this happen. I guess it's another way of getting it done and I will study it in details to understand if it could work for me. Thanks!!!!

[RE_]

Correction about the date calculation. When you finish studying the tutorial, you will find that the dynamic report CROSSTAB does not use that YYYY-MM calculation.

Can put that conditional hours calculation in textboxes of report.

Hi June7,

Now that I'm importing the new queries into the main database I'm realising that I can't use the IFF function in the report's textboxes. The trick wouldn't work with the textboxes that I use for the employees' totals when more than one project is allocated to one employee and the real total goes above 160. I would need to show the real amount of hours if this happens.

Is there a way to use the IFF function in the query (or a workaround)?

Thanks!!!!