Group by week days

[childrenfirst]

Hi,

I have to create an Access report based on data in a SQL table. The table has four fields, EmployeeName, Timeoffstartdate, Timeoffenddate, and Offtype (there are two types: sick day, paid leave).

The report needs to show which week days (i.e. Monday, Tuesday, Wednesday, and etc.) people call in and take sick days. The challenge is that the SQL table only record the startdate and enddate, and I don't know how to convert the time-off date range (from startdate to enddate) to weekdays and count by weekdays.

Any advice or suggestion will be greatly appreciated!

Thank you

 

[misscrf]

I believe you can do a date format (see function Format) and choose MMMM as the format. Then group on it. Please let me know if you need more than that, and I will try to research with you.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You can use the Weekday() function to find the day of the week of any date value. If you need to include all "between" dates, then provide more complete requirements and possibly sample data and results.

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]

 

[childrenfirst]

Hi Duane,

Here is an example. There are 5 columns in the SQL table:

TimeoffID Employeename Startdate Enddate Timeofftype
1 Andy 11/01/2006 11/03/2006 Sick Day
2 Bill 11/02/2006 11/03/2006 Sick Day

I need to create a report that shows the total count of sick days all employees took by weekday:

Mon Tue Wed Thu Fri
0 0 1 2 2

Can this be done with the Weekday() function?

Thank you!

 

[misscrf]

Is it just me or is that a cross tab? (I only ask because I know nothing about them).

I would think to put a setup like this together:

LabelMonday LabelTuesday etc.

In a query, I would have a field that looks like this:

MyMonday:IIF(Weekday(YourDate)=Monday,Monday,"") Then do a count on this field. Repeat this for each day.

Does that work?



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[childrenfirst]

It's not a crosstab. The query should be able to count the weekdays in each date range and total the counts group by weekday... Hope this makes sense...

 

[dhookom]

ChildrenFirst,
You can't count between dates without between date records. Create a table [tblDates] of dates [TheDate] and add records for every date from start to finish. Add this table to a query with the [SQL] table and set the criteria under [TheDate] to

Between [Startdate] And   [Enddate]

You can then create a Crosstab query with a Column Heading of: Format([TheDate],"ddd")
Create a Count of TheDate as the value. Set whatever fields you want as the Row Headings (perhaps Timeofftype).


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]

 

[childrenfirst]

Duane,

I am not sure how easy it can be done, but I have an idea based on yours:

Create a stored procedure with two parameters (OffStartDate and OffEndDate) generated from an Access form for the Access report. The stored procedure will first select records within this date range from the TimeOff table and throw them into a Temp table which shows all the between dates for each record, convert each date into weekday (with maybe DATENAME(dw,TheDate) statement), and then count the total count by Weekdays for all records in the temp table.

Does it sound logical and feasible to you?

Thank you!

 

[dhookom]

Why create a temp table when you can use a pre-built table of all dates? Does your solution create multiple actual records with the same date? Your solution sounds like it would work but I would rather not have to create records where they are not needed.

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]

 

[mckenziemh]

I know this was from quite a while ago, but I thought I might give some insight to this problem.

Depending on how big your existing table is already, I would recommend changing it a bit. It would be best to add a new record for each day the employee is off instead of a date range.

Once you have done something along those lines, you can use the following for a "By Day" report

Example for column "Mon"

Mon:SUM(IIF(WEEKDAY([yourdatecolumn])="Monday",1,0))


I know I'm quite a bit late in this game, but I hope it helps a little!

 

[dhookom]

mckenziemh,
The WeekDay() function will not return a value like "Monday". Your expression WEEKDAY([yourdatecolumn])="Monday" will always return False.

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]

 

[mckenziemh]

Okay, I was wrong with the initial logic of the formula, but I've done a little research and found this solution. I've tested it, and it seems to work just fine.

Access, unless otherwise defined, assumes that Sunday is the first day of the week. So, you can use the day assignments as follows to get your report:

Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7

For Column "Mon"

Mon:SUM(IIF(Weekday([yourdatecolumn])=2,1,0))

Tailor this formula for each day, then group your query by employee name and it will show you, by weekday, which days have been taken off.

Thanks,
mckenziemh