Displaying Previous Week's Data in Subreport

[mejia.j88]

Hello all,

I have a report in which the day's production is displayed.


The report shows basic information involving yield according to shift, part number, etc etc.
At the end of the report, I have a line that shows the overall yield of the day's production; in other words, it adds up the In quantities and the Out quantities and shows the percentage accepted of all parts.

i would like to create a sort-of subreport in which I can show the overall yield for the previous week, the one before that, etc.

ideally, i would want to show data for the last 5 weeks or so.

I am not sure how to address this idea correctly, or how to set up a query(ies) that will allow me to structure what I want.

Any ideas? please feel free to share.

Thank you in advance,

j

[June7]

Sounds like a subreport with grouping of data by week. The query could be set up with a field that calculates week number based on a date field and then criteria would limit the records to those for the previous 5 weeks including the current week.

[mejia.j88]

hi june7,

the part im having trouble with is setting up the field that calculates week number based on the date field.
how would i set up the field so that it shows for five different days.

right now i have a query (on paper, haven't made it yet) which would have a field called Week One with the criteria Between Date()-1 and Date()-8. Suppose the report is viewed today, then week one would show the data from between Yesterday 6/28 and 6/21, which would be one week.

would i have to create 4 other different fields with similar formulas date-9 and date-16, date-17 and date-24 etc. ?

or is there a way to have it work with one field only?

thanks

[June7]

Calculate week number with:

Datepart("ww",date) will return week of year
Datepart("w",date) will return week of month

So in query:
SELECT *, Datepart("ww",date) As WeekNum, Year(date) As Yr FROM tablename;

Then criteria under WeekNum could be:
>Datepart("ww",date)-5

If data is multi-year, probably also want to include year criteria.