Using date ranges

[Guest]

Using date ranges

I need to create a form or report that displays info from the last four
weeks (including this week). i want it to look like a table (which can
actually just be text boxes aranged like a table). It will look something
like:

1/24/05 1/31/05 2/7/05 2/14/05 2/28/05
Total Hours 45 42 40 40 40
Number of Units 125 153 110 105 113
Man Hour per unit 0.36 0.27 0.36 0.38 0.35

The Man Hour per unit is just a simple calculation of the above rows. Assume
this week is 2/28/05 (AKA week 9), each week could be calculated as 9-1, 9-2,
etc. I want to enter in a popup parameter query for this weeks start date,
aka 2/28/05, and it would spin this into the particular text boxes
representing the appropriate week (this week, this week -1, this week -2,
etc). Luckily this all comes from one table called tblManHourStats.

What is the best way to make this work, from the report side, as well as the
query to call this up? How would you best lay out the query and the control
source for the text boxes to pull from this query? I'm also not quite sure
what the best method of controling the weeks (preferably thru the query).
I've not actually made this type of "date range" query work yet.

Any help on this would be an awesome time saver. There's not enough time in
the day to get this done at work.

 

[Duane Hookom]

Do you have a table structure and some sample records you would like to
share? To view how I would set up the weeks view this link
http://www.mcse.ms/archive154-2004-5-706789.html.

 

[Guest]

imagine the following table feeding the output from the previous message:

Date Total Hours Number of Units Man hours per unit
1/24/05 45 125 0.36
1/31/05 42 153 0.27
2/7/05 40 110 0.36
2/14/05 40 105 0.38
2/28/05 40 113 0.35

of course there will be more weeks and other un related fields, but this is
the core info.

Does this help? I will look into the link at home, but if I can avoid VBA,
i'd be better off. I'm lousey with it. Thanks.

 

[Duane Hookom]

The link I provided uses no VBA.
If your dates are always Mondays, you might consider looking at the crosstab
reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The
CrosstabReport.mdb might provide some ideas for you.

 

[Guest]

After looking closer at it over lunch break, the link you first directed me
to doesn't contain VBA, but is a SQL statement. It will definitely point me