Query weekly and monthly data for year (running sum?)

  • Thread starter Thread starter Michael Bentfeld
  • Start date Start date
M

Michael Bentfeld

Hello,

I'm trying to set up a database to allow my company to
track the amount of time our mechanics are spending on
various types of repair work. Each record contains the
mechanic's name, their ID code, the date the work was
done, what type of repair work it was (warranty repair,
product updates, customer repair, demo unit repair), the
accounting code associated with each repair type, and the
number of hours spent on the work. I need to create a
couple of reports that will display a year's worth of
data, one the data broken down by employee and the other
broken down by repair code. Both reports need to show
that year's worth of data with totals for each week (M-F)
and each month.

I tried setting the reports up to have the user specify
the date(s) desired, but this was found by the user to be
too cumbersome. I want to set up the report so it looks
at the date on the computer, and automatically searches
back one year. Does anyone have any suggestions on how to
set up a query for the report to look back through one
year's worth of records? Also, does anyone know how to
set the reports to give weekly and monthly totals for the
hours worked? Any help I can get would be greatly
appreciated.

Thanks,

Michael
 
I want to set up the report so it looks
at the date on the computer, and automatically searches
back one year. Does anyone have any suggestions on how to
set up a query for the report to look back through one
year's worth of records?
Click to expand...

Use a criterion on the datefield of

BETWEEN DateAdd("yyyy", -1, Date()) AND Date()

to get (for today) all records between 10/21/2002 and 10/21/2003; to
get year-to-date starting January 1, use

BETWEEN DateSerial(Year(Date()), 1, 1) AND Date()
Also, does anyone know how to
set the reports to give weekly and monthly totals for the
hours worked?
Click to expand...

You may need two Subreports for this, since the same data is being
totalled in different ways; one would be based on a Query grouped by

DatePart("ww", [datefield])

and the other grouped by

Month([datefield])
 
Back
Top