These two queries will do it except as it is it is pull from all records, not
just the last 7 days. You will need to add to the criteria your starting
point in time.
Justin_1 --
SELECT [Driver Hours].Hours_ID, [Driver Hours].Hours_Date
FROM [Driver Hours]
WHERE ((((SELECT [XX].On_Duty_Hours FROM [Driver Hours] AS [XX] WHERE
[XX].Hours_ID = [Driver Hours].Hours_ID AND [XX].Hours_Date =
DateAdd("d",-1,[Driver Hours].Hours_Date))+[Driver Hours].[On_Duty_Hours])=0))
ORDER BY [Driver Hours].Hours_ID, [Driver Hours].Hours_Date;
SELECT [Driver Hours].ID, [Driver Hours].Hours_ID, [Driver
Hours].Hours_Date, [Driver Hours].On_Duty_Hours, (SELECT SUM(On_Duty_Hours)
FROM [Driver Hours] t
WHERE t.Hours_ID = [Driver Hours].Hours_ID AND iif(Justin_1.Hours_Date is
null, t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver
Hours].Hours_Date, t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and
[Driver Hours].Hours_Date AND t.Hours_Date > Justin_1.Hours_Date )) AS
HoursLast7
FROM [Driver Hours] LEFT JOIN Justin_1 ON [Driver Hours].Hours_ID =
Justin_1.Hours_ID
ORDER BY [Driver Hours].Hours_ID, [Driver Hours].Hours_Date;
--
Build a little, test a little.
Justin said:
If you will post your actual SQL maybe some one can suggest a method for you.
Open query in design view and click on VIEW - SQL View, hightlight all,
copy, and paste in a post.
--
Build a little, test a little.
Justin said:
On Feb 22, 3:35 pm, KARL DEWEY <
[email protected]>
wrote:
t.Hours_Date BETWEEN [Driver Hours].Hours_Date -6 and [Driver
Hours].Hours_Date)
I was examining this to decide on an approach and discovered this will not
work as it is requiring that [Driver Hours].Hours_Date be between itself and
itself minus 6 days.
Maybe you want [Driver Hours].Hours_Date to be between Date() and Date()-6
or maybe between Max([Driver Hours].Hours_Date) and Max([Driver
Hours].Hours_Date)-6 or even a date entered at a prompt.
:
This is a subquery that Duane helped me with to calculate hours for
the previous 7 days:
HoursLast7: (SELECT SUM(On_Duty_Hours)
FROM [Driver Hours] t
WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN
[Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date)
What I would like to do is to have the total reset to zero if any two
consecutive days in the previous 7 equal zero. Do I need a separate
query/subquery or can this be done inside of the current one?
This query works as it is. I just need to have the total to reset zero
if any two previous days equal zero.
.
Here it is again.
SELECT [Driver Hours].ID, [Driver Hours].Hours_ID, [Driver
Hours].Hours_Date, [Driver Hours].On_Duty_Hours, (SELECT
SUM(On_Duty_Hours)
FROM [Driver Hours] t
WHERE t.Hours_ID = [Driver Hours].Hours_ID AND t.Hours_Date BETWEEN
[Driver Hours].Hours_Date -6 and [Driver Hours].Hours_Date) AS
HoursLast7
FROM [Driver Hours]
ORDER BY [Driver Hours].Hours_ID, [Driver Hours].Hours_Date;
Thanks
Justin Thomas
.
