Weekly crosstab from daily crosstab! (1 Viewer)

[BEPGroup]

I've created a crosstab that returns the following:

Row header: Dates (eg 1/5/13, 2/5/13, 8/5/13) - dd/mm/yy
Column headings: Monday, Tuesday, Wednesday, Thursday, Friday
Values: different text strings (eg, / \ O B etc)

At the moment the crosstab returns one record for each date. I want to present the data as one record (or line) for each week starting with the date for the Monday of that week.

Hope this makes sense! Any suggestions?

Thanks

 

[PNGBill]

You could add Calculated Fields to your data (query) that uses WeekNumber
This additionl field should allow grouping in your Crosstab.

 

[Pat Hartman]

Try posting the SQL so we can figure out what is wrong.

 

[BEPGroup]

I'm very new to access and still at the dangerous stage, thinking I know more than I actually do! The data is actually attendance data from a daily (weekday) register. Week one actually starts on Monday 3rd September 2012 as the year is an academic year, school holidays aren't counted as weeks and week 39 (the last week in a school year) is towards the end of July in the following calender year (2013). Having read some similar posts I think the solution is to build a lookup table of dates with a corresponding week number, by which I can group in the crosstab. I didn't know about "ww" returning a week number but that will be useful in the future I suspect!

 

[BEPGroup]

I wanted to avoid having to create a lookup table each year but I can't see another way!

 

[PNGBill]

I wanted to avoid having to create a lookup table each year but I can't see another way!

Maybe you could have a table of significant dates. eg Term start and finish dates. Public holidays etc.
this may allow your code to refer to this table when dealing with a given year.
Where this would differ from a full year able is the annual input is less but not avoided.

 

[Pat Hartman]

There is no need to create a date table. You'll just have to maintain it. There are at least two functions that can extract the week number from a date. Just use that. And the cross tab query will group by week also.

 

[BEPGroup]

There is no need to create a date table. You'll just have to maintain it. There are at least two functions that can extract the week number from a date. Just use that. And the cross tab query will group by week also.

Thanks Pat. What are the two functions? Can I set the function to start at week 1 from any date - specifically Monday 3rd September 2012?

 

[Pat Hartman]

Week() and Format()

If you want to learn VBA, the place to start is to find a reference for all the functions. Use a list grouped by type rather than sorted alphabetically. The alpha list is only useful if you actually know the name of the function you want to use. The grouped list gives you a limited number of items to search through to find a "date" function or a "financial" function, etc.

 

[21Aberdeen]

I need help in crosstab query output to generate employee leave records calendar (transpose)with below data field/type .

Name Date_From Date_To Lv_type
Barney 07-Nov-13 10-Nov-13 PL
Sarah 11-Dec-13 12-Dec-13 AL
Scott 22-Nov-13 28-Nov-13 R&R
Steven 13-Dec-13 19-Dec-13 R&R

Thank you