Query one week's records, etc.

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

Michael Bentfeld

Hello,

I have the following problem:

I'm creating a database to track labor performed by our
company's mechanics by type of labor performed. The
fields are:

ID (The mechanics' initials)
DATE (The date the work was performed)
LABOR TYPE (Type of labor performed (warranty repairs,
customer repairs, etc.))
LABOR CODE (3-letter code for each labor type)
HOURS (Number of hours of labor performed)

I want to set up three different queries for this
database. These queries would pull all labor records for
the past week, month, and year respectively. How can I
set up criteria for each of these queries to find records
through one week, month, or year prior to the current
date? Any suggestions would be greatly appreciated.
Thanks.

Michael
 
I want to set up three different queries for this
database. These queries would pull all labor records for
the past week, month, and year respectively. How can I
set up criteria for each of these queries to find records
through one week, month, or year prior to the current
date?
Click to expand...

The DateDiff() function will help here. Create a Query based on your
table with a criterion on the [DATE] field (note: Date is a reserved
word, and Access may very well get confused - you may want to change
this to WorkDate or something):
= DateAdd("ww", -1, Date()) for weeks
= DateAdd("m", -1, Date()) for the past month
= DateAdd("yyyy", -1, Date()) for the past year
Click to expand...

All of these calculate an entire time unit - that is, if you use the
Month query today you'll get all work done since September 20, 2003.
If you want month-to-date or year-to-date you can use the DateSerial
function instead:
 
It would be helpful to know what your cutoff points are
for the time frames: for instance, for past week, does
this end on a certain day of the week.

A parameter query might be useful if it's conceivable the
user will enter the cutoff period.

There are several options that are pretty simple. Let me
know and I'll try to help you!

Thanks,
Loretta
 
The cutoff day would likely be Friday, since the mechanics
only work Monday through Friday. Hope that helps.

Thanks,

Michael
 
Here's a workaround (definitely not the most efficient),
but without having more information it's the quickest I
could come up with:

For the Week Query: Prompt the user to enter the start
and end date by setting up a query that has the following
criteria in the Date field:

Between [Enter Week Start Date] And [Enter Week Ending
Date]

For the Month Query: Prompt the user to enter the last
day of the month for the desired month by entering the
following Criteria in the date field. (For March, user
would type 3/31/03....)

Between DateSerial((Year([MonthEndDate])),(Month
([MonthEndDate])),1) And [MonthEndDate]

For the Year Query: Prompt the user for the year by
adding the following field to the query pane:

Expr1: [Enter Year]
The criteria for this field would be: Year([Date])

Loretta
 
I'll give it a shot and let you know how it turns out.

Thanks,

Michael
-----Original Message-----
Here's a workaround (definitely not the most efficient),
but without having more information it's the quickest I
could come up with:

For the Week Query: Prompt the user to enter the start
and end date by setting up a query that has the following
criteria in the Date field:

Between [Enter Week Start Date] And [Enter Week Ending
Date]

For the Month Query: Prompt the user to enter the last
day of the month for the desired month by entering the
following Criteria in the date field. (For March, user
would type 3/31/03....)

Between DateSerial((Year([MonthEndDate])),(Month
([MonthEndDate])),1) And [MonthEndDate]

For the Year Query: Prompt the user for the year by
adding the following field to the query pane:

Expr1: [Enter Year]
The criteria for this field would be: Year([Date])

Loretta
-----Original Message-----
Hello,

I have the following problem:

I'm creating a database to track labor performed by our
company's mechanics by type of labor performed. The
fields are:

ID (The mechanics' initials)
DATE (The date the work was performed)
LABOR TYPE (Type of labor performed (warranty repairs,
customer repairs, etc.))
LABOR CODE (3-letter code for each labor type)
HOURS (Number of hours of labor performed)

I want to set up three different queries for this
database. These queries would pull all labor records for
the past week, month, and year respectively. How can I
set up criteria for each of these queries to find records
through one week, month, or year prior to the current
date? Any suggestions would be greatly appreciated.
Thanks.

Michael

.
Click to expand...
.
Click to expand...
 
Back
Top