Prev: getting data from date range
Next: Action Query (Append) didn't add records due to key violations.
From: John Spencer on 21 Mar 2010 16:02
I haven't come up with a good way to do this with just a query. I've put
together a function and tested it minimally. This assumes that your lookup
table has a weekday number (1 to 7) and an hour number (0 to 23).

Also this gives full credit for partial hours.


'================= Minimally tested code follows ================
Function fCalcSum(dStart, tStart, dEnd, tEnd)
Dim dblResult As Double
Dim StrSQL As String
Static WeekTotal As Double

If WeekTotal = 0 Then
WeekTotal = dSum("TheValue", "ValuesTable")
End If

If IsDate(dStart) And IsDate(tStart) _
And IsDate(dEnd) And IsDate(tEnd) Then

If Weekday(dStart) <= Weekday(dEnd) Then
StrSQL = " SELECT SUM(TheValue)" & _
" FROM ValuesTable" & _
" WHERE DayNumber*100 + HourNumber >= " & _
Weekday(dStart) * 100 + Hour(tStart) & _
" AND DayNumber*100 + HourNumber <= " & _
Weekday(dEnd) * 100 + Hour(tEnd)
ElseIf Weekday(dStart) > Weekday(dEnd) Then
StrSQL = " SELECT SUM(TheValue)" & _
" FROM ValuesTable" & _
" WHERE DayNumber*100 + HourNumber <= " & _
Weekday(dEnd) * 100 + Hour(tEnd) & _
" OR DayNumber*100 + HourNumber >= " & _
Weekday(dStart) * 100 + Hour(tStart)
End If
'Get the hours that are not entire weeks
dblResult = Nz(CurrentDb().OpenRecordset(StrSQL).Fields(0), 0)

'Get hours for entire week(s)
dblResult = dblResult + (DateDiff("d", dStart, dEnd) \ 7) * WeekTotal

'Adjust the total for counting the same hour twice in the above SQL
dblResult = dblResult - 1
'Return the resulting calculation
fCalcSum = dblResult
Else
fCalcSum = Null
End If

End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John Spencer wrote:
> I wish you had answered all the questions, but that should be enough for
> me to start thinking about a solution. I would like to do this using a
> query, but it may be necessary to create a VBA function. It would
> certainly be easier to use VBA to solve this, but the speed might not be
> acceptable. I will try to get back to you by Sunday. I am a little
> stretched for time at this point.
>
>
>
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Martin wrote:
>> Thanks for the reply John.
>>
>> Yes I do have records that could start on a Friday and end on the
>> Tuesday of the next week. Table one with the data of start date/time
>> and end date/time is something I am sent and cannot change however the
>> second table with the percentages is something I have created to try
>> to help overcome the problem but this can be changed if that helps?
>>
>>
>>
>> "John Spencer" wrote:
>>
>>> Sounds as if you need a non-equi join and at the same time you need
>>> to do some data manipulation on the fields That are being used in the
>>> join.
>>>
>>> The first thing I would do would be to add another column and store
>>> the Day of week number Sun=1 to Sat=7 for each record. Then I would
>>> add another column that stores just the NUMBER of the hour.
>>>
>>> How do you handle partial hours? Ignore them, round them to the
>>> nearest whole hour, or use partial hours to get partial credit.
>>>
>>> Do you have records that overlap days? I assume so since you are
>>> recording a start date and end date
>>>
>>> Do you have records that encompass more than one week - event starts
>>> on Friday and runs until Tuesday of the following week? If not the
>>> problem becomes a bit easier to solve.
>>>
>>> Do you have records that encompass multiple weeks? Jan 1 to Jan 31
>>> for instance.
>>>
>>> John Spencer
>>> Access MVP 2002-2005, 2007-2010
>>> The Hilltop Institute
>>> University of Maryland Baltimore County
>>>
>>> Martin wrote:
>>>> Hello,
>>>>
>>>> I am looking for some advice on how to tackle querying some data.
>>>> I have two tables, table one with around 10,000 records per month
>>>> and columns that give me (for each record) a start date, start time,
>>>> end date and end time.
>>>>
>>>> Table two details a percentage for each hour and day of the week (so
>>>> 24 hours * 7 days = 168 records). This has three columns, the day of
>>>> the week (Mon to Sun), the hour (00:00 to 23:00) and a percentage.
>>>>
>>>> What I want to do is sum the total of the percentage column in table
>>>> 2 where the day of the week and the hour of the week fall within the
>>>> start date / start time and end date / end time.
>>>>
>>>> Any help would be greatly appreciated.
>>>>
>>>> Martin
>>> .
>>>
First  |  Prev  | 
Pages: 1 2
Prev: getting data from date range
Next: Action Query (Append) didn't add records due to key violations.