determine week of the month, not year

[spottednewt]

I have a report where I want detail records to be grouped horizontally (in columns) by week ending date. For example, if an employee works hours at different jobs during a month, I want to have the employee name in the header, and then 4 columns of total hours representing each week of that month, broken out by job type. I have a week ending date attached to each record, so it's easy to group vertically, I just don't know how to do it in columns. Any tips would be greatly appreciated.

 

[dhookom]

I would try a crosstab query that uses the datediff in weeks for the column headings.

Their are 12 months in a year and about 52 weeks. Are you working with a 13 month year or how do you plan on handling this issue?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[spottednewt]

really it's by week ending date. so my preference would be to group records in columns by week ending date.

For example, if i had the last 3 days of june at the beginning of the week, the week ending date would actually be a july date. But I still need it grouped with the month of june. That is why i thought i could group the individual records by the date stamp of each record based on the week of the month instead of the year.

I tried the crosstab query but the labels wouldn't refresh on the report.
I haven't used the datediff before but maybe that would work.

 

[dhookom]

Check this faq703-5466 and change the monthly increment to weekly.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]