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.
[CODE=sql]
SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRan ge([Event Date]) AS [Week Range],
Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
FROM Table1
GROUP BY CInt(Format$([Event Date],"ww")), fConvertWeekRan ge([Event Date]);[/CODE]
[CODE=vb]
Public Function fConvertWeekRan ge(dteEventDate As Date) As String
Dim intWeekNumber As Integer

intWeekNumber = CInt(Format$(dt eEventDate, "ww"))

Select Case intWeekNumber
Case 1
fConvertWeekRan ge = "Jan. 1, 2008 to Jan. 5, 2008"
Case 2
fConvertWeekRan ge = "Jan. 6, 2008 to Jan. 12, 2008"
Case 3
fConvertWeekRan ge = "Jan. 13, 2008 to Jan. 19, 2008"
Case 4
fConvertWeekRan ge = "Jan. 20, 2008 to Jan. 26, 2008"
Case 5
fConvertWeekRan ge = "Jan. 27, 2008 to Feb. 2, 2008"
Case 6
fConvertWeekRan ge = "Feb. 3, 2008 to Feb. 9, 2008"
Case 7
fConvertWeekRan ge = "Feb. 10, 2008 to Feb 16, 2008"
Case 8
fConvertWeekRan ge = "Feb. 17, 2008 to Feb. 23, 2008"
Case 9
fConvertWeekRan ge = "Feb. 24, 2008 to Mar. 1, 2008"
'etc...
Case 10
Case Else
fConvertWeekRan ge = ""
End Select
End Function[/CODE]
Sample OUTPUT:
[CODE=text]
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[/CODE]

[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.
[CODE=sql]
SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRan ge([Event Date]) AS [Week Range],
Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
FROM Table1
GROUP BY CInt(Format$([Event Date],"ww")), fConvertWeekRan ge([Event Date]);[/CODE]
[CODE=vb]
Public Function fConvertWeekRan ge(dteEventDate As Date) As String
Dim intWeekNumber As Integer

intWeekNumber = CInt(Format$(dt eEventDate, "ww"))

Select Case intWeekNumber
Case 1
fConvertWeekRan ge = "Jan. 1, 2008 to Jan. 5, 2008"
Case 2
fConvertWeekRan ge = "Jan. 6, 2008 to Jan. 12, 2008"
Case 3
fConvertWeekRan ge = "Jan. 13, 2008 to Jan. 19, 2008"
Case 4
fConvertWeekRan ge = "Jan. 20, 2008 to Jan. 26, 2008"
Case 5
fConvertWeekRan ge = "Jan. 27, 2008 to Feb. 2, 2008"
Case 6
fConvertWeekRan ge = "Feb. 3, 2008 to Feb. 9, 2008"
Case 7
fConvertWeekRan ge = "Feb. 10, 2008 to Feb 16, 2008"
Case 8
fConvertWeekRan ge = "Feb. 17, 2008 to Feb. 23, 2008"
Case 9
fConvertWeekRan ge = "Feb. 24, 2008 to Mar. 1, 2008"
'etc...
Case 10
Case Else
fConvertWeekRan ge = ""
End Select
End Function[/CODE]
Sample OUTPUT:
[CODE=text]
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[/CODE]

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:
[CODE=sql]
SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRan ge([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")), fConvertWeekRan ge([Event Date]);[/CODE]

[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:
[CODE=sql]
TRANSFORM Sum(Table1.[Trip Hours]) AS [SumOfTrip Hours]
SELECT Table1.Employee ID, Table1.[Employee Name], Sum(Table1.[Trip Hours]) AS [Total Of Trip Hours]
FROM Table1
GROUP BY Table1.Employee ID, Table1.[Employee Name]
PIVOT "Qtr# " & Format([Event Date],"q");[/CODE]

[Alex30093]

Try this alternate approach, and let me know what you think:
[CODE=sql]
SELECT CInt(Format$([Event Date],"ww")) AS [Week Number], fConvertWeekRan ge([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")), fConvertWeekRan ge([Event Date]);[/CODE]

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.

[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 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

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 responsibilitie s 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 responsibilitie s 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.