Crosstab and In function

[RE_]

Hi amazing people,

I need a bit of help with a “In” function in a crosstab (I’m not 100% sure it would be the right function though).
Please refer to the attached db.
I’ve created a crosstab as the data currently included in the table should be shown differently. The values are hours and they are allocated to various employees by weeks which I need to show in columns instead of rows. Problem is I can’t just use the weeks as headers because the data in the table would need to be refreshed every week and then… the headers will change.
I was thinking that I could use the In function to transform the real weeks headers to 1,2,3,4 (In 1,2,3,4) and afterwards use a formula in a report which will convert the 1,2,3,4 back to the real weeks. As you can imagine I’ve already tried to incorporate the In function in the crosstab without success and I believe that this is due to the fact that I need to format the weeks first (but I can’t figure out how). Is anyone able to please assist me?
Thaaaaaaanks!

[Minty]

Your are trying to create dynamic report headings.
This article is perhaps as good as any to explain how: http://www.fmsinc.com/MicrosoftAcces...ort/index.html

[Minty]

Looking at your data - you would be much better storing you week/year data as an actual date.
You can then work out the week of the year, and consequently number your weeks per report period (Is it always a four week period?)

[RE_]

Hi Minty,

Thank you very much for your answer.

I've now included real dates and I've tried to use the In function in the new crosstab.

Problem is that, as you already suspected, it is not always a four week period. Also Using that formula the hours don't get summed up.

Ideally I would like to still have a crosstab that I can use for reports in a weekly basis and perhaps even for a specific month. Any ideas?

Thank you very much again!

[June7]

Why is date not in a date/time field?

Access expects dates as mmddyyyy, yours are ddmmyyyy. I suspect the calculation will fail because of that. I am in U.S. and cannot work with date having international structure. Review http://allenbrowne.com/ser-36.html

I added a date/time field to table and converted the string to correct U.S. date. Using the new field, query now shows data in column 1 whereas before neither column had data. For testing, instead of Now(), I used #6/1/2022# and this outputs data into both columns.

BTW, can use Date() instead of Now() since the time parts are not needed.

You want to number the weeks within each month? Access doesn't have an intrinsic function that can number weeks of month like there is for weeks of calendar year. Review https://stackoverflow.com/questions/...m-a-given-date

Advise not to use spaces in naming convention.

[RE_]

Hi June7,

Thanks for your answer!

I followed your advise and formatted the column as date (what an idiot I am), removed the space in the header and use Date() instead of Now().

It seems that somehow my db has no problem with the dd/mm/yyyy format. Please see the screenshot attached. I have checked the results against the original data shown in the table and they're correct.

Going back to my original question, I guess now I really don't care about what header is shown in the new crosstab as long as I keep using the same query even when I refresh the data next month (I will remove the data for June and input July's data in it). So I believe that even having 1,2,3,4,5 would do I guess? I'm a bit lost with code shown in stackoverflow. I'm really not good with VBA. I have pasted the code in a module but how can I use this new function in SQL to give me a result similar to 1,2,3,4,5?

[June7]

Replace the expression currently used with a call of the function

PIVOT WeekOfMonth([CalendarDay]) IN(1,2,3,4,5,6)

Probably need to add a filter to pull only June records or delete the July records or use additional row grouping criteria:

Format([CalendarDay], "yyyyMM")

[RE_]

Hi June7,

Thanks for your kindness.

I guess I'm getting there but you're right, I can't have 5 weeks because atm the first 2 weeks (first week in June and first week in July) will get summed up together. And I believe the results would be even worse if I import data starting mid-month instead of the beginning of each month.

Not sure on how to interpret your Format function to fix this? Is it just adding the format to to the query? Tried to do it myself but it doesn't give me any results.

How could I add a filter?

Thanks again!!!!!

[CJ_London]

I don't see anywhere where you define what a week is.

What day does Week 1 start?
1st of Month regardless?
first monday?/sunday? of the month
nearest monday to the 1st?
something else?

Is every week a full 7 days? or are the weeks at the beginning/end of the month truncated to only include the month?

[RE_]

I don't see anywhere where you define what a week is.

What day does Week 1 start?
1st of Month regardless?
first monday?/sunday? of the month
nearest monday to the 1st?
something else?

Is every week a full 7 days? or are the weeks at the beginning/end of the month truncated to only include the month?

Hi CJ_London,

The dates are based on a fiscal calendar. The dates I'll see in the system will always be Fridays which is the week ending. So for some weird reasons it's a Saturday-Friday week.
Week 1 in a month will be the first Friday which means that in the data I've included in the db attached above, Week1 is 03-06-2022 (dd-mm-yyyy) and the related hours started to be accrued from 28-05-2022.
Every week will always be 7 days.
Hope it makes sense :-D

[CJ_London]

so the last week of a month will end on the last friday.

And assuming your data is correct in that it only reports Friday dates, This works

PARAMETERS [Enter a Year/Month (yyyymm)] Text ( 255 );
TRANSFORM Sum(data.Total) AS SumOfTotal
SELECT data.Employee
FROM data
WHERE (((Format([calendar_day],"yyyymm"))=[Enter a Year/Month (yyyymm)]))
GROUP BY data.Employee, Format([calendar_day],"yyyymm")
PIVOT (Day([calendar_day])\7)+1

for June you get

Employee	1	2	3	4
10063812	27	45	14	40
10091779				3
10099236	1	1
10106805	18	31	40	40
10107021		30	50	50

for july

Employee	1
10063812	45
10091779	2
10106805	40
10107021	20
10116097	40
10116159	40

[RE_]

so the last week of a month will end on the last friday.

And assuming your data is correct in that it only reports Friday dates, This works

PARAMETERS [Enter a Year/Month (yyyymm)] Text ( 255 );
TRANSFORM Sum(data.Total) AS SumOfTotal
SELECT data.Employee
FROM data
WHERE (((Format([calendar_day],"yyyymm"))=[Enter a Year/Month (yyyymm)]))
GROUP BY data.Employee, Format([calendar_day],"yyyymm")
PIVOT (Day([calendar_day])\7)+1

for June you get

Employee	1	2	3	4
10063812	27	45	14	40
10091779				3
10099236	1	1
10106805	18	31	40	40
10107021		30	50	50

for july

Employee	1
10063812	45
10091779	2
10106805	40
10107021	20
10116097	40
10116159	40

Oh wow amazing!!! Splendid! That is fantastic! Thank you both sooo much!!!!

[June7]

If you want to build a stable report based on this crosstab, will need the IN() clause so the same fields will always be generated and textboxes bound to those fields will not fail.