Extract data from previous week, previous two weeks, etc.

[Kurt]

I have a table of data with details about hourly work done on a
project. A typical snapshot looks like this:

9/6 Did ABC 3:00PM 4:00PM 1 hr
9/6 Did DEF 4:30PM 5:00PM .5 hr
9/10 Did XYZ 9:00AM 2:00PM 5 hrs
....
9/15 Did DEF 11:00AM 12:00PM 1 hr
etc.

I need to generate two kinds of reports from this data:

2) a weekly report every Monday that summarizes work performed for the
previous week (defined as the previous Monday through Sunday).

3) a biweekly invoice every two Mondays that summarizes work performed
for the previous two weeks. The invoice should show, for each week and
a total for both weeks, the hours spent and the amount to be billed
(hours * hourly rate). For example:

9/6-9/12 Did ABC;DEF;XYZ 6.5 hrs $50 $325
9/13-9/19 Did DEF 1 hr $50 $150
-------------------------------------------------------------------------
TOTAL 7.5 hrs $50 $475

(Concatenating the task data into one string as shown would be nice.)

How can I write a query to pull data from the previous week or two
week period? Is the best approach a parameter query where I enter
today's date (e.g., the Monday that I'm generating the update or
invoice), and then have the query extract and summarize all data that
fall within the previous 7 (or 14) days? Any other suggestions?

Thanks.

 

[John Spencer]

Criteria for Previous Week - works with any date in the current week

Between DateAdd("d",-5-WeekDay(Date()),Date()) and
DateAdd("d",1-WeekDay(Date()),Date())

Previous TWO weeks

Between DateAdd("d",-12-WeekDay(Date()),Date()) and
DateAdd("d",1-WeekDay(Date()),Date())

For your other problem of concatenating the data into one string it can be
done using VBA. Here are links (url) to three examples of concatenating a
field from multiple records into one field in one record.

Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

I should have mentioned that you can use the DateAdd expressions that are in
the where clause as sources for controls on the report. You would just need
to add an equals sign to the expression.

Text box control
Name: PeriodStart
Control Source: = DateAdd("d",-5-WeekDay(Date()),Date())

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County