Summarizing hourly into weekly

[JordanR]

Title kind of says it. I have a table with:
InDate, InHour, Data1, Data2... etc.

I wanted a query that would summarize the data up into weeks, but what makes it more difficult is a "work week" is Monday-Sunday. Any easy way to do this?

 

[MSAccessRookie]

Title kind of says it. I have a table with:
InDate, InHour, Data1, Data2... etc.

I wanted a query that would summarize the data up into weeks, but what makes it more difficult is a "work week" is Monday-Sunday. Any easy way to do this?

If you think of a work week as existing between WeekStartDate and dateadd("d", 6, WeekStartDate), then it should not matter what day of the week that a work week starts or ends on, and you can use those dates as a basis for your calculations.

Will that be of any use to you?

 

[JordanR]

It does, but I'm not sure how that translates to the Query screen. If I had to do this in VBA, I think what you're describing would help me. But I'm not sure how I'd define WeekStartDate so I could do that calculation

 

[MSAccessRookie]

You can determine the last Monday before any given date (WEEKSTART) via the following Function:

Public Function fnFindMonday(InDate As Date)
    If Weekday(InDate) = vbMonday Then
        fnFindMonday = InDate
    Else
        fnFindMonday = fnFindMonday(InDate - 1)
    End If
End Function

You can determine the next Sunday after any given date (WEEKEND) via the following Function:

Public Function fnFindSunday(InDate As Date)
    If Weekday(InDate) = vbSunday Then
        fnFindSunday = InDate
    Else
        fnFindSunday = fnFindMonday(InDate + 1)
    End If
End Function

Then something like the following would give you the hours worked last week:

Select EmployeeName, Sum(HoursWorked)
From EmployeeTimeTable
Where ((DateWorked >= fnFindMonday(DateAdd("w",-1,Date())) And 
       (DateWorked <= fnFindSunday(DateAdd("w",-1,Date()))))

 

[JordanR]

So I copy/pasted the code into functions.
Then created a new query with the following:

SELECT Sum(HourlyIn.[numberofstuff])
FROM HourlyIn
WHERE ((HourlyIn.InDate>=fnFindMonday(DateAdd("w",-1,Date())) And (HourlyIn.InDate<=fnFindSunday(DateAdd("w",-1,Date())))));

And all I get is undefined function 'fnFindMonday' in expression. I've checked and double checked that the functions are public.

Any help?

 

[MSAccessRookie]

So I copy/pasted the code into functions.
Then created a new query with the following:

SELECT Sum(HourlyIn.[numberofstuff])
FROM HourlyIn
WHERE ((HourlyIn.InDate>=fnFindMonday(DateAdd("w",-1,Date())) And (HourlyIn.InDate<=fnFindSunday(DateAdd("w",-1,Date())))));

And all I get is undefined function 'fnFindMonday' in expression. I've checked and double checked that the functions are public.

Any help?

Where did you put the functions? They belong in a VB Module associated with the entire project, not just one form. I placed them in a new module called CommonFunctions, but they could have been placed in any existing module as well. I tried this before I put it here and it worked for me.

 

[JordanR]

Closer and closer I get. I realized I had the stupid security settings turned on, so the function was disabled.
I got rid of that nonsense and now when I run the query it runs, but no data is returned. To explain a little more, the table looks somethign like this;

InDate InTime Value
01/01/2008 0:00 100
01/01/2008 1:00 57
01/01/2008 2:00 94

and so on, for the entire year. I'm trying to generate a query that sums up Value weekly, where the week begins on a Monday and ends on a Sunday.
The code you gave me seems to do just that, but the query returns nothing when I run it.

Thank you for all your help.

 

[JordanR]

Still can't figure this out. If you have any more ideas, I'm all ears.

 

[MSAccessRookie]

So I copy/pasted the code into functions.
Then created a new query with the following:

SELECT Sum(HourlyIn.[numberofstuff])
FROM HourlyIn
WHERE ((HourlyIn.InDate>=fnFindMonday(DateAdd("w",-1,Date())) And (HourlyIn.InDate<=fnFindSunday(DateAdd("w",-1,Date())))));

And all I get is undefined function 'fnFindMonday' in expression. I've checked and double checked that the functions are public.

Any help?

I think there was a parenthesis mismatch. Maybe it caused the problem? Try this instead.

    SELECT Sum(HourlyIn.[numberofstuff]) 
    FROM HourlyIn
    WHERE ((HourlyIn.InDate >= fnFindMonday(DateAdd("w",-1,Date())) 
    And    (HourlyIn.InDate <= fnFindSunday(DateAdd("w",-1,Date()))));

 

[JordanR]

Nope, looks like you were missing a parenthesis in your latest post. I added it back and get the same results.

 

[neileg]

Can't you use the GroupOn property in a report to group by week? I know this runs Sunday to Saturday but you'd just need to adjust your dates by a day.

 

[jal]

Title kind of says it. I have a table with:
InDate, InHour, Data1, Data2... etc.

I wanted a query that would summarize the data up into weeks, but what makes it more difficult is a "work week" is Monday-Sunday. Any easy way to do this?

The first question is what you mean by "summary".
If you want to sum-total a numeric column by week, you'll likely need to develop a GROUP BY clause that groups by week. This sort of thing isn't easy, but I may have a sample in my notes that will help....

OR, perhaps you want to add a column that labels each record as "Week1" or "Week2" or whatever. Then sort the table on that column, so that the user can see what data is for week1, what data is for week2, and so on. This isn't easy either (at least not for me).

Which is it you want?

 

[jal]

Ok, here's what I found in my notes.
To summarize by week means that you want to regard all the dates for that week as under the same category(say Week1) and thus as dups of each other (as the same 'date' so to speak). So you need a way to change all that date values of that week to the same date. Like this.
GROUP BY InvoiceDate-Weekday(InvoiceDate) + 1
for example let's suppose InvoiceDate is day 5. What we have, then, is

GROUP BY (Day 5)-Weekday([Day 5) + 1 .... = 1
Notice the result is 1. No matter what day you plug in (whether Day 4, or Day 3, or whatever), you'll always get 1. So what this does is make all the dates for that week the same date (Day 1). However, we want to start on Monday, the 2nd day of the week, so let's put a 2 in that formula:

GROUP BY InvoiceDate-Weekday(InvoiceDate,2)+1
And you can put it in your SELECT as well

SELECT InvoiceDate-Weekday(InvoiceDate,2)+1
From...
GROUP BY InvoiceDate-Weekday(InvoiceDate,2)+1

However, you might want to format the SELECT, maybe something like this


SELECT FORMAT(InvoiceDate-Weekday(InvoiceDate,2)+1, "ww") as TheWeekNumber
From...
GROUP BY InvoiceDate-Weekday(InvoiceDate,2)+1
Haven't tested any of this. Gotta go...

 

[JordanR]

Jal,
What I'm going for is a SUM of daily data into weekly data
Summing numerical values that I have for Monday, Tuesday, Wednesday, etc into a value for Week 1, Week 2, Week 3 (of the year).
the problem is that a "week" is Monday through Sunday instead of Sunday through Saturday.

 

[jal]

Jal,
What I'm going for is a SUM of daily data into weekly data
Summing numerical values that I have for Monday, Tuesday, Wednesday, etc into a value for Week 1, Week 2, Week 3 (of the year).
the problem is that a "week" is Monday through Sunday instead of Sunday through Saturday.

In that case the advice I gave is probably appropriate. You would be probably looking for a GROUP BY as described above. If you have any sample data, consider uploading (Access 2003 format). If not, maybe I can plug in a few numbers of my own.

 

[jal]

Yep, seems to work ok. Let's pretend we're summing revenue per week. The following query gives you that result. The weeks are numbered from 1 to 52, in the sample data (attached) the span is two weeks (weeks 41 and 42 of this year).

SELECT 'Week ' + FORMAT(theDate-Weekday(theDate,2)+1, "ww") as theWeekNo, SUM(Revenue) as RevenueForThisWeek
From Table1
GROUP BY theDate-Weekday(theDate,2)+1