Query One table with Multiple Date Ranges

[Alex30093]

OK This is my 1st post to any forum, but I rely on forums all the time. Short story is I find myself needing to become a Access DBA for work.

Situation: I want to use one table of events that Track (Employee ID, Employee Name, Event Date and Trip Hours) And be able to add the fields(Week #1, Week #2, Week#3, week #4 and Week #5). *****There is also an Autonumbered (Trip Numebr).

What I have tried: I can run this as a query (Employee ID, Employee Name, Event Date and Trip Hours). I can setup various date ranges and it runs great, but that is only with one date range. Multiple ranges return a null query.

This is one of my attempts: Week #1:IIf([Event Date],Between #3/9/2008# And #3/15/2008#, sum[Total Hours],”0”)

Problem: What is the best way to summarize the [Total Hours] based on weekly date ranges that reference the [Event Date]?


Thank you very much,
-Alex

[whitbacon]

In the query use format to as follow:

Format([eventdate], "w")

then group by on the employee id and the above column and sum on the hours field

[ADezii]

OK This is my 1st post to any forum, but I rely on forums all the time. Short story is I find myself needing to become a Access DBA for work.

Situation: I want to use one table of events that Track (Employee ID, Employee Name, Event Date and Trip Hours) And be able to add the fields(Week #1, Week #2, Week#3, week #4 and Week #5). *****There is also an Autonumbered (Trip Numebr).

What I have tried: I can run this as a query (Employee ID, Employee Name, Event Date and Trip Hours). I can setup various date ranges and it runs great, but that is only with one date range. Multiple ranges return a null query.

This is one of my attempts: Week #1:IIf([Event Date],Between #3/9/2008# And #3/15/2008#, sum[Total Hours],”0”)

Problem: What is the best way to summarize the [Total Hours] based on weekly date ranges that reference the [Event Date]?


Thank you very much,
-Alex

Assuming you are filtered for a specific/current Year, I'll post some sample code and leave the rest up to you (I allowed for January to March 1). This will probably provide you with more than you need, but you can easily eliminate what you don't want. I've assumed your Table Name is Table1.

SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRange([Event Date]) AS [Week Range], 
Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
FROM Table1
GROUP BY CInt(Format$([Event Date],"ww")), fConvertWeekRange([Event Date]);

Public Function fConvertWeekRange(dteEventDate As Date) As String
Dim intWeekNumber As Integer
 
intWeekNumber = CInt(Format$(dteEventDate, "ww"))
 
Select Case intWeekNumber
  Case 1
    fConvertWeekRange = "Jan. 1, 2008 to Jan. 5, 2008"
  Case 2
    fConvertWeekRange = "Jan. 6, 2008 to Jan. 12, 2008"
  Case 3
    fConvertWeekRange = "Jan. 13, 2008 to Jan. 19, 2008"
  Case 4
    fConvertWeekRange = "Jan. 20, 2008 to Jan. 26, 2008"
  Case 5
    fConvertWeekRange = "Jan. 27, 2008 to Feb. 2, 2008"
  Case 6
    fConvertWeekRange = "Feb. 3, 2008 to Feb. 9, 2008"
  Case 7
    fConvertWeekRange = "Feb. 10, 2008 to Feb 16, 2008"
  Case 8
    fConvertWeekRange = "Feb. 17, 2008 to Feb. 23, 2008"
  Case 9
    fConvertWeekRange = "Feb. 24, 2008 to Mar. 1, 2008"
  'etc...
  Case 10
  Case Else
    fConvertWeekRange = ""
End Select
End Function

Sample OUTPUT:

Week Number    Week Range                        Total Hours for Range
1            Jan. 1, 2008 to Jan. 5, 2008               100
2            Jan. 6, 2008 to Jan. 12, 2008               143
3            Jan. 13, 2008 to Jan. 19, 2008                 7
4            Jan. 20, 2008 to Jan. 26, 2008                56
5            Jan. 27, 2008 to Feb. 2, 2008               100
6            Feb. 3, 2008 to Feb. 9, 2008                53
7            Feb. 10, 2008 to Feb 16, 2008                61
8            Feb. 17, 2008 to Feb. 23, 2008               146
9            Feb. 24, 2008 to Mar. 1, 2008               153

[Alex30093]

In the query use format to as follow:

Format([eventdate], "w")

then group by on the employee id and the above column and sum on the hours field

--------------------------------------------------------------------------------------------

I tried that before and then double checked it based on your suggestion. However, that just puts them in sequence without summarizing the hours. So for one employee, over four weeks I may have as high as 22 rows of data in sequence. I can use four summarized rows ( one for each week). Or in a perfect situation I would like to have:

One entry (Row) have all of the employee data (Employee ID, Employee Name) then with 4 additional (Columns) as Week numbers 1-4. That way at a glance I can see how those 22 events were distributed within those 4 weeks.

This is all part of a payroll function: This part is the data that I can import from another program. The other parts are manual. Once I get this part then I can work on reporting the manual data into a simular table of just totals

[Alex30093]

Assuming you are filtered for a specific/current Year, I'll post some sample code and leave the rest up to you (I allowed for January to March 1). This will probably provide you with more than you need, but you can easily eliminate what you don't want. I've assumed your Table Name is Table1.

SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRange([Event Date]) AS [Week Range], 
Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
FROM Table1
GROUP BY CInt(Format$([Event Date],"ww")), fConvertWeekRange([Event Date]);

Public Function fConvertWeekRange(dteEventDate As Date) As String
Dim intWeekNumber As Integer
 
intWeekNumber = CInt(Format$(dteEventDate, "ww"))
 
Select Case intWeekNumber
  Case 1
    fConvertWeekRange = "Jan. 1, 2008 to Jan. 5, 2008"
  Case 2
    fConvertWeekRange = "Jan. 6, 2008 to Jan. 12, 2008"
  Case 3
    fConvertWeekRange = "Jan. 13, 2008 to Jan. 19, 2008"
  Case 4
    fConvertWeekRange = "Jan. 20, 2008 to Jan. 26, 2008"
  Case 5
    fConvertWeekRange = "Jan. 27, 2008 to Feb. 2, 2008"
  Case 6
    fConvertWeekRange = "Feb. 3, 2008 to Feb. 9, 2008"
  Case 7
    fConvertWeekRange = "Feb. 10, 2008 to Feb 16, 2008"
  Case 8
    fConvertWeekRange = "Feb. 17, 2008 to Feb. 23, 2008"
  Case 9
    fConvertWeekRange = "Feb. 24, 2008 to Mar. 1, 2008"
  'etc...
  Case 10
  Case Else
    fConvertWeekRange = ""
End Select
End Function

Sample OUTPUT:

Week Number    Week Range                        Total Hours for Range
1            Jan. 1, 2008 to Jan. 5, 2008               100
2            Jan. 6, 2008 to Jan. 12, 2008               143
3            Jan. 13, 2008 to Jan. 19, 2008                 7
4            Jan. 20, 2008 to Jan. 26, 2008                56
5            Jan. 27, 2008 to Feb. 2, 2008               100
6            Feb. 3, 2008 to Feb. 9, 2008                53
7            Feb. 10, 2008 to Feb 16, 2008                61
8            Feb. 17, 2008 to Feb. 23, 2008               146
9            Feb. 24, 2008 to Mar. 1, 2008               153

This was not exactly what I was looking for but it will be extremely helpful as we look at monthly trends.

Thank you very much,
-Alex

[ADezii]

--------------------------------------------------------------------------------------------

I tried that before and then double checked it based on your suggestion. However, that just puts them in sequence without summarizing the hours. So for one employee, over four weeks I may have as high as 22 rows of data in sequence. I can use four summarized rows ( one for each week). Or in a perfect situation I would like to have:

One entry (Row) have all of the employee data (Employee ID, Employee Name) then with 4 additional (Columns) as Week numbers 1-4. That way at a glance I can see how those 22 events were distributed within those 4 weeks.

This is all part of a payroll function: This part is the data that I can import from another program. The other parts are manual. Once I get this part then I can work on reporting the manual data into a simular table of just totals

Try this alternate approach, and let me know what you think:

SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRange([Event Date]) AS [Week Range], 
Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
FROM Table1
WHERE ((Year([Event Date])=2008))
GROUP BY CInt(Format$([Event Date],"ww")), fConvertWeekRange([Event Date]);

[ADezii]

How about a Crosstab Query approach, the following Crosstab Query will summarize the Trip Hours for each EmployeeID/Employee by breaking the Event Dates into Quarters. Modify the Pivot Section to change the Date structure:

TRANSFORM Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
SELECT Table1.EmployeeID, Table1.[Employee Name], Sum(Table1.[Trip Hours]) AS [Total Of Trip Hours]
FROM Table1
GROUP BY Table1.EmployeeID, Table1.[Employee Name]
PIVOT "Qtr# " & Format([Event Date],"q");

[Alex30093]

Try this alternate approach, and let me know what you think:

SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRange([Event Date]) AS [Week Range], 
Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
FROM Table1
WHERE ((Year([Event Date])=2008))
GROUP BY CInt(Format$([Event Date],"ww")), fConvertWeekRange([Event Date]);

I am a week into playing with Access so I don't understand a lot of this. But I put it into SQL view and did what you suggested. It says "undefined function 'fConvertWeekRa nge' in expression".

In previous attempts I put the dates in a weekly "ww" format. Thinking that similar weeks could be easily grouped and combined. Do I need to define a date range somewhere?

[Stewart Ross]

Hi. If you review post #3 above you will see that ADezii posted code for you that included the skeleton of the custom function fConvertWeekRan ge he wrote as part of his suggested solution on your behalf. Either you have not added that function to a public code module (the named modules which show in the Module window of the database), or you have added it to a private code module (such as the code module for a form).

I would suggest that you need to consider what has already been provided to you by ADezii before loooking at other alternatives...

-Stewart

I am a week into playing with Access so I don't understand a lot of this. But I put it into SQL view and did what you suggested. It says "undefined function 'fConvertWeekRa nge' in expression"...

[ADezii]

I am a week into playing with Access so I don't understand a lot of this. But I put it into SQL view and did what you suggested. It says "undefined function 'fConvertWeekRa nge' in expression".

In previous attempts I put the dates in a weekly "ww" format. Thinking that similar weeks could be easily grouped and combined. Do I need to define a date range somewhere?

But I put it into SQL view and did what you suggested. It says "undefined function 'fConvertWeekRa nge' in expression".

The Function must be declared as Public in a Standard, not Form, Code Module.

In previous attempts I put the dates in a weekly "ww" format. Thinking that similar weeks could be easily grouped and combined. Do I need to define a date range somewhere?

"ww" used in conjunction with the Format() Function will return a numeric value indicating the Week Number within the Year (1 to 52). If all you need is this along with the Summary Totals for each Week, then the solution is quite simple. This is not, however, what you requested.