Sum for previous (x) number of records in detail

  • Thread starter Thread starter Justin
  • Start date Start date
J

Justin

I have created a report that lists three columns of data for each
employee. The column consists of a date field and another field that
contains the number of hours on duty for that day. Each column
represents one month of data. What I need is to be able to have a sum
for each record that sums that particular day plus the previous 7
days. The sum would also need to include some data that would not be
shown on the report. (Days prior to the first month shown on the
report) Hopefully I've explained my problem sufficiently enough.
Thanks for your help.

Justin
 
Your table is not designed correctly! Your table should look like:
TblEmployeeDuty
EmployeeDutyID
EmployeeID
DutyDate
HoursOnDuty

To get the current day plus the previous seven days put the following
expression in the criteria of the DutyDate:
Between DateAdd("d",-7,Date()) And Date()

Toget the sum of the hours on duty in that time period, you need a Totals
query. Click on the Sigma button (looks like a capital E)in the menu at the
top of the screen. Then change Group By under
HoursOnDuty to Sum.

Steve
(e-mail address removed)
 
Your table is not designed correctly! Your table should look like:
TblEmployeeDuty
EmployeeDutyID
EmployeeID
DutyDate
HoursOnDuty

To get the current day plus the previous seven days put the following
expression in the criteria of the DutyDate:
Between DateAdd("d",-7,Date()) And Date()

Toget the sum of the hours on duty in that time period, you need a Totals
query. Click on the Sigma button (looks like a capital E)in the menu at the
top of the screen. Then change Group By under
HoursOnDuty to Sum.

Steve
(e-mail address removed)
Click to expand...

I actually have two tables in this particular db. One consists only of
the first and last names of drivers along with an autonumber ID field
that is used to link to the other table where I have the following
fields:
ID Autonumber
Hours_ID number foreign key to first table
Hours_Date Date
On_Duty_Hours Number Single

The On_Duty_Hours field is the one that I need to have the sum for.
I'm not sure that I'm following what you're referring to as to the
criteria for the date field. I've tried using the Sum grouping in a
query without the results I require. Perhaps someone can point me a
little closer to the right direction, because I apparently can't read
very well right now.

Thanks
Justin
 
I actually have two tables in this particular db. One consists only of
the first and last names of drivers along with an autonumber ID field
that is used to link to the other table where I have the following
fields:
ID       Autonumber
Hours_ID    number   foreign key to first table
Hours_Date     Date
On_Duty_Hours      Number  Single

The On_Duty_Hours field is the one that I need to have the sum for.
I'm not sure that I'm following what you're referring to as to the
criteria for the date field. I've tried using the Sum grouping in a
query without the results I require. Perhaps someone can point me a
little closer to the right direction, because I apparently can't read
very well right now.

Thanks
Justin
Click to expand...


Well, I see now why I haven't been getting any results with the
proposed condition for the Date field in the query. I couldn't see for
looking I guess. I'm going to post back when I get my head wrapped
around what I really need because I don't think I have explained what
I need properly.

Thanks again for your replies, they have pointed me in the right
direction at least.

Justin
 
Well, I see now why I haven't been getting any results with the
proposed condition for the Date field in the query. I couldn't see for
looking I guess. I'm going to post back when I get my head wrapped
around what I really need because I don't think I have explained what
I need properly.

Thanks again for your replies, they have pointed me in the right
direction at least.

Justin
Click to expand...

Here we go again.
What I need in my report is to have the date, on duty hours and a sum
that includes that particular day plus the previous seven days. This
sum needs to be for each day in the detail.

12/10/09 12.00
12/11/09 12.00
12/12/09 12.00
12/13/09 12.00
12/14/09 12.00
12/15/09 12.00 72.00
12/16/09 12.00 84.00
12/17/09 12.00 84.00

I'm also going to group this data by driver and by quarter. I would
also like to have each month in the quarter in a separate column
(three columns of data for each page/quarter. I hope this little
example explains what I would like to have a little better.

Thanks for all your help.
Justin
 
Back
Top