Weeks / year to date report

[Rikkid]

I am trying to setup a report that displays a weeks labout figure but also along side it puts in a year to date value.

i.e.

If the underlaying data looks like this

04/06/02 $400 John
04/06/02 $300 Andy
04/13/02 $200 John
04/13/02 $100 Andy
04/20/02 $200 John
04/20/02 $300 Andy

If you chose the query to pick up data from 01/06/02 to weekending 04/20/02 your report would show

Labour Report as at week Ending 04/20/02

Name Weeks value Year to Date
John $200 $800
Andy $300 $700

I can do a weeks figure on one report and do year to date an a seperate report I just cannot seem to do them on one.

I can do a query that you put in the date range of the year to date and put in a report that does a the year to date ut I cannot get it to sort in w/ending order and also show just the weeks value and also sub total it and then do a grand total for the period over the whole report.

Any ideas gratefully received.

Thanks

 

[mdthornton]

Use something like this to solve your week/ytd problem.

select name, sum(iif(week_ending = report_week_ending, value, 0)) as week_total, sum(value) as ytd_total
group by name

Note: "report_week_ending" above is your date range parameter. The above does the work for you in the input query to the report but you could equally well do it within the report.

I didn't understand your second problem.... Best Regards,
Mike

 

[MichaelRed]

Three queries:

Crosstab for the aggregates. Weekending for the current week. Join on the name.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over