A Year to Date Report Question

[garylm]

I need to create a YTD Report in the following format:

CurrMonth PrevMonth CurrYear
PrevYear
Customer1 2 1 7
5
Customer2 6 19 15
12 etc...

CurrMonth: This month
PrevMonth: Same month last year
CurrYear: This year up to and through CurrMonth
PrevYear: Last year up to and including PrevMonth

I have a query that gathers the records I need from a single table but for
the life of me I can't retreive the sum of quantities I need for the pivot
action. Is there a way to create the report without a Query or how can I
extract the numbers from my query and total them on the report? Please help!!
! The hair I haven't pulled out is completely gray.

Gary[dash]Mitchell[AT]live[DOT]com

 

[Jeff Boyce]

Is there a reason you wish not to use a query? Queries work quite well to
gather the information you need to show in a report.

If you can create a query (or a "chain" of queries) that returns your
totals, could you use that to create your report?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Marshall Barton]

I need to create a YTD Report in the following format:

CurrMonth PrevMonth CurrYear
PrevYear
Customer1 2 1 7
5
Customer2 6 19 15
12 etc...

CurrMonth: This month
PrevMonth: Same month last year
CurrYear: This year up to and through CurrMonth
PrevYear: Last year up to and including PrevMonth

I have a query that gathers the records I need from a single table but for
the life of me I can't retreive the sum of quantities I need for the pivot
action. Is there a way to create the report without a Query or how can I
extract the numbers from my query and total them on the report?

Try something like this, but don't expect it to be any
faster than a pig in mud.

SELECT qryCurrYear.customer,
Sum(IIf(dtfield Between DateSerial(Year(Date())-1,
Month(Date()), 1) And DateSerial(Year(Date())-1,
Month(Date()) + 1, 0), amount, 0)) As PrevMonth,
Sum(IIf(dtfield Between DateSerial(Year(Date())-1, 1, 1)
And DateSerial(Year(Date())-1, Month(Date()) + 1, 0),
amount, 0)) As PrevYear,
Sum(IIf(dtfield Between DateSerial(Year(Date()),
Month(Date()), 1) And DateSerial(Year(Date()), Month(Date())
+ 1, 0), amount, 0)) As CurrMonth,
Sum(IIf(dtfield Between DateSerial(Year(Date()), 1, 1)
And DateSerial(Year(Date()), Month(Date()) + 1, 0), amount,
0)) As CurrYear
FROM table
WHERE whatever