Prev: Publish a report to excel?
Next: Drawing a grid on a Report
From: garylm on 19 Mar 2010 19:37
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

From: Jeff Boyce on 19 Mar 2010 19:54
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.

"garylm" <u58859(a)uwe> wrote in message news:a543750efc12c(a)uwe...
>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
>


From: Marshall Barton on 19 Mar 2010 20:33
garylm wrote:

>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

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Publish a report to excel?
Next: Drawing a grid on a Report