Query rolling dates?

P

pvdalen

Hi all,

I don't know if I'm drawing a blank and missing something easy and obvious,
but what I'd like to to is take my table that has 2 date columns and build a
query that rolls through the dates, seaching for any 7 day block that meets a
certain condition. I just can't figure out how to do it. I could probably
export the data to an Excel sheet and play with VBA to do that, but I'm
thinking there has to be a way I could do it through a query.

Thanks in advance,
Paul
 
J

John Vinson

Hi all,

I don't know if I'm drawing a blank and missing something easy and obvious,
but what I'd like to to is take my table that has 2 date columns and build a
query that rolls through the dates, seaching for any 7 day block that meets a
certain condition. I just can't figure out how to do it. I could probably
export the data to an Excel sheet and play with VBA to do that, but I'm
thinking there has to be a way I could do it through a query.

Thanks in advance,
Paul
Click to expand...

I'm sure there is, but I don't understand the question!

You have two date fields. Which field are you searching? What
constitutes a "block"?


John W. Vinson[MVP]
 
P

pvdalen

Hey John,

Sorry if I wasn't clear enough.

I have a table with 4 fields, DateIn, TimeIn, DateOut, TimeOut, that
correspond to activities done by a given worker. The worker can work up to
24 hours in a day, but can't exceed 140 hours in any 7-day period, so I'd
like to query for any 7-day period within the weeks of data I have to
identify those workers that exceed 140 hours worked.

If you're wondering why I spilt the date and timein and out into seperate
field, it's for ease of data entry. With the amount of data we have, typing
the entire date/time string is a pain. Just typing a few characters each for
the date and time fields that Access recognizes is a lot easier. If that
sounds ponderously stupid to you, feel free to let me know.

And, in case you're wondering, I don't store the hours worked in another
field. I concatenate the date/time, then find the difference whenever I need
it.

Thanks again,
Paul
 
O

Olli Kinnunen

pvdalen said:
Hi all,

I don't know if I'm drawing a blank and missing something easy and obvious,
but what I'd like to to is take my table that has 2 date columns and build a
query that rolls through the dates, seaching for any 7 day block that meets a
certain condition. I just can't figure out how to do it. I could probably
export the data to an Excel sheet and play with VBA to do that, but I'm
thinking there has to be a way I could do it through a query.

Thanks in advance,
Paul
Click to expand...
Paul,
I understood you are after a running total within a window ?

Pls see the simplified solution below.Instead of your Date&Time and
Hours worked this example uses Item as Key and Value as data.
It is basically two running totals, phased by a number of rows and the
difference calculated.

SELECT tblbase.item AS ItemAlias, tblbase.value,
DSum("Value","tblBase","[Item]<=" & [ItemAlias] & "") AS Running1,
DSum("Value","tblBase","[Item]<=" & ([ItemAlias]-6) & "") AS Running2,
[Running1]-[Running2] AS RunningTotalWindowed
FROM tblbase
GROUP BY tblbase.item, tblbase.value
ORDER BY tblbase.item;


see also http://support.microsoft.com/default.aspx?scid=kb;en-us;132134

I believe you can modify this for your solution
 
Top