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 'fConvertWeekRange' 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 fConvertWeekRange 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 'fConvertWeekRange' 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 'fConvertWeekRange' 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 'fConvertWeekRange' 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.

[Alex30093]

Hi. If you review post #3 above you will see that ADezii posted code for you that included the skeleton of the custom function fConvertWeekRange 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

Stewart & ADezii,

Thank you, as you point it out ADezii's post does contain a way to create the ranges I am looking for. I am just so new to any form of code. I am a network specialist who just got the DBA responsibilities handed to me. So I am totally out of my area of strenght.

In looking at the answer with the "private code module" I just have no clue where to put that. I just got the Access for Dummies book so I can develope the basic knowledge to be able to impliment these suggestions.

ADezeii I am sorry your advice just went over my head.

I will study some more and see how to work with what you all have given me.


Thanks again,
-Alex

[ADezii]

Stewart & ADezii,

Thank you, as you point it out ADezii's post does contain a way to create the ranges I am looking for. I am just so new to any form of code. I am a network specialist who just got the DBA responsibilities handed to me. So I am totally out of my area of strength.

In looking at the answer with the "private code module" I just have no clue where to put that. I just got the Access for Dummies book so I can develop the basic knowledge to be able to implement these suggestions.

ADezeii I am sorry your advice just went over my head.

I will study some more and see how to work with what you all have given me.


Thanks again,
-Alex

Alex, nothing to be sorry for. Being handed the job of a DBA, even given the fact that you are a Network Specialist, is no small task. Let's take a different approach on this matter. How about providing me some realistic Test Data, say consisting of a couple hundred Records, if possible. I'll try to provide a couple of solutions for you in the form of Querys which you can then view and analyze. These Queries and the Test Database itself I will make available to you as an Attachment to a Post (a picture is worth a thousand words). If you feel as though this is a good idea, let me know, and I'll send you my E-Mail Address in a Private Message. You can then Attach the Data to an E-Mail Message sent to me.

[Alex30093]

Alex, nothing to be sorry for. Being handed the job of a DBA, even given the fact that you are a Network Specialist, is no small task. Let's take a different approach on this matter. How about providing me some realistic Test Data, say consisting of a couple hundred Records, if possible. I'll try to provide a couple of solutions for you in the form of Querys which you can then view and analyze. These Queries and the Test Database itself I will make available to you as an Attachment to a Post (a picture is worth a thousand words). If you feel as though this is a good idea, let me know, and I'll send you my E-Mail Address in a Private Message. You can then Attach the Data to an E-Mail Message sent to me.

Thanks that would be awesome.

[Alex30093]

Alex, nothing to be sorry for. Being handed the job of a DBA, even given the fact that you are a Network Specialist, is no small task. Let's take a different approach on this matter. How about providing me some realistic Test Data, say consisting of a couple hundred Records, if possible. I'll try to provide a couple of solutions for you in the form of Querys which you can then view and analyze. These Queries and the Test Database itself I will make available to you as an Attachment to a Post (a picture is worth a thousand words). If you feel as though this is a good idea, let me know, and I'll send you my E-Mail Address in a Private Message. You can then Attach the Data to an E-Mail Message sent to me.

I don't know if you tried to send me a private message or not. I am just checking in.
-Alex

[ADezii]

I don't know if you tried to send me a private message or not. I am just checking in.
-Alex

Alex, I did send you my E-Mail Address in a Private Message but I'll send it again.

[ADezii]

Thanks that would be awesome.

I've taken your basic Excel data, imported it into Access, randomly assigned names to the Employee IDs, then dumped it into a Table named tblAlex30093. From what you have previously indicated, you have no problem getting to this point. I then created an Intermediate Query which properly formats the data and assigned weekly intervals for the [Dep Date] Field. This Query is named qryTotalHours. The final step in this process was to create a Crosstab Query that summarizes Total Hours for each Employee, for specific Week Intervals. Total Sales spanning all Intervals is also calculated. I've manually restricted the Date Range from 3/1/2008 to 3/31/2008 and worked only on a subset of the data. You can make the Crosstab Query accept Date Parameters if you wish, and you can also include a greater Date Range. The Crosstab only works for the year 2008 right now. Enough already! Just download the Attachment Alex30093.zip, and let me know what you think.

[Alex30093]

I've taken your basic Excel data, imported it into Access, randomly assigned names to the Employee IDs, then dumped it into a Table named tblAlex30093. From what you have previously indicated, you have no problem getting to this point. I then created an Intermediate Query which properly formats the data and assigned weekly intervals for the [Dep Date] Field. This Query is named qryTotalHours. The final step in this process was to create a Crosstab Query that summarizes Total Hours for each Employee, for specific Week Intervals. Total Sales spanning all Intervals is also calculated. I've manually restricted the Date Range from 3/1/2008 to 3/31/2008 and worked only on a subset of the data. You can make the Crosstab Query accept Date Parameters if you wish, and you can also include a greater Date Range. The Crosstab only works for the year 2008 right now. Enough already! Just download the Attachment Alex30093.zip, and let me know what you think.

Thanks I will work on it and get back to you.