2 week intervals in Crosstab qry columns

[mlrucci]

Good afternoon,


I am attempting to create a crosstab query with the row headings as employee and the column header to show in two week intervals based on today's date and going back one year. I have created a from with txtDateStart0 as the start date to allow changes in the date as needed for recalculation for different start dates. To be clearer, I need a sum of hours for each employee of their sick time so see if they are out of compliance at any time within a date range in a two week interval. Example below. I am having difficulty with the column headers to calculate based on two week intervals. All I could fine is quarterly. Can anyone help?

Employee 10/13/18 to 9/30/18 to
10/13/17 9/30/17

Frank 16 32
Betty 8 10

[mlrucci]

My appologies,
After posting the thread, I noticed that the format for my quick example was not the same as I inputted. please see below. My txtDateStart0 on the form is 10/13/18. There will be a total of 26 columns when completed so that I can see a full 2 years back from txtDateStart0

Employee	10/13/18 to 10/13/17	09/30/18 to 09/30/17
Frank	16	32
Betty	8	10

Thank you in advance for you assistance.

[CJ_London]

There will be a total of 26 columns when completed so that I can see a full 2 years back from txtDateStart0

if 26 columns and 2 week intervals, that is 52 weeks

suggest review the partition function in sql, which will get you your 2 week intervals, but your column headings cannot be sorted to provide the right order (they are text), so you will also need another column to count backwards and sort on that - perhaps use the datedff function to return weeks difference, it won't matter what the value is, 2, 4, 6 will sort the same as 1,2, 3 and won't be shown in your final query

[June7]

I see each column header is a full year, but 'rolling' by 2 weeks. Is that what you really want? It means data will be reported under multiple headings. Don't think PARTITION can accomplish that.

This might need domain aggregate functions (DCount, DSum) in 26 calculated fields or textboxes.

[CJ_London]

ahh - didn't notice that, thought it was 2 week periods, not a moving total, scrap partition then.

On closer reading, I don't think the proposed solution meets the requirement anyway

I need a sum of hours for each employee of their sick time so see if they are out of compliance at any time within a date range in a two week interval

which implies over a two week period from any sick date. Analysing in two week periods doesn't work because someone may have had say 3 days in the second half of one period and 3 days in the first half of the next period.

Think the OP needs to clarify