Creating a sum to include a particular day plus the previous 7 days

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

Justin

I need to create a total on a report that sums hours from one day with
the previous 7 days for each day of the month. I will also group this
by driver, quarter and month.

Thanks in advance for your time and effort.

Justin
 
How about providing significant information such as field names, sample
records, and desired display?

Duane Hookom
MS Access MVP
 
I need to create a total on a report that sums hours from one day with
the previous 7 days for each day of the month. I will also group this
by driver, quarter and month.

Thanks in advance for your time and effort.

Justin
Click to expand...

You can do this by creating an appropriate query for your table structure.

If you would like help doing so please post some information about that
structure, and perhaps an example of a couple of rows of the data. An example
of what you mean by "previous 7 days for each day" would help too - it's not
clear to me what this means.
 
How about providing significant information such as field names, sample
records, and desired display?

Duane Hookom
MS Access MVP
Click to expand...

The table I am using has four fields:
ID, autonumber, Hours_ID, foreign key to another table, Hours_Date,
Date/Time and Hours, Number, Single

I need the report to group on Drivers(foreign key connects to this
table), date by quarter and by month. Under each month I need to show
the date in one column and the daily hours in another column. Then I
need another column to sum the daily hours from a particular day
through the previous seven days. Each page will show an entire
quarters worth of data for each driver. I have created a report that
shows the data the way I want it to, but getting the sum mentioned is
stumping me.

The form I am using lists the drivers in the main form and the subform
lists the Hours_ID, Hours_Date and Hours. I am entering the date and
daily hours in the subform for each driver.

I hope that this is enough information to enable you to help me.

Thanks again
Justin
 
Do the hours from the previous 7 days include all drivers or does each driver
have their own previous 7 days.

I would probably calculate this in the report's record source query using a
subquery.
 
Do the hours from the previous 7 days include all drivers or does each driver
have their own previous 7 days.

I would probably calculate this in the report's record source query usinga
subquery.
Click to expand...


Duane, the previous 7 days would be for each driver. I tried using
DateAdd, but it only returns todays date plus the previous 7 days. I
need for this calculation to be from any date. Perhaps you can suggest
something for the subquery.
Thanks

Justin
 
Assuming Hours_ID is the driver link, I would try create a query like the
following to see if the numbers are accurate.

SELECT ID, Hours_ID, Hours_Date, Hours,
(SELECT SUM(Hours)
FROM [NoNameGiven] t
WHERE t.Hours_ID = NoNameGiven.Hours_ID AND
t.Hours_Date BETWEEN NoNameGiven.Hours_Date - 7 and
NoNameGiven.Hours_Date) As HoursLast7
FROM NoNameGiven;
 
Assuming Hours_ID is the driver link, I would try create a query like the
following to see if the numbers are accurate.

SELECT ID, Hours_ID, Hours_Date, Hours,
(SELECT SUM(Hours)
 FROM [NoNameGiven] t
 WHERE t.Hours_ID = NoNameGiven.Hours_ID AND
 t.Hours_Date BETWEEN NoNameGiven.Hours_Date - 7 and
   NoNameGiven.Hours_Date) As HoursLast7
FROM NoNameGiven;

--
Duane Hookom
Microsoft Access MVP

Duane, the previous 7 days would be for each driver. I tried using
DateAdd, but it only returns todays date plus the previous 7 days. I
need for this calculation to be from any date. Perhaps you can suggest
something for the subquery.
Thanks
Click to expand...
Justin
.
Click to expand...
Click to expand...

Thanks, Duane. I've been away from work for a few days, but I will
give your suggestion a try in a few days.

Justin
 
Assuming Hours_ID is the driver link, I would try create a query like the
following to see if the numbers are accurate.
Click to expand...
SELECT ID, Hours_ID, Hours_Date, Hours,
(SELECT SUM(Hours)
 FROM [NoNameGiven] t
 WHERE t.Hours_ID = NoNameGiven.Hours_ID AND
 t.Hours_Date BETWEEN NoNameGiven.Hours_Date - 7 and
   NoNameGiven.Hours_Date) As HoursLast7
FROM NoNameGiven;
Click to expand...

Thanks, Duane. I've been away from work for a few days, but I will
give your suggestion a try in a few days.

Justin
Click to expand...

That did the trick, Duane. Thanks a million.
 
Back
Top