Prev: Reporting Out Two Names w/Phone # from the Same Table
Next: Show query Parameter in Chart?
From: magicdds on 1 Jul 2008 01:56
This is a little tricky but works really well.
Thanks for your help.

Mark



"Allen Browne" wrote:

> A crosstab is the most obvious way to get each year into a column of its
> own, but the issue here is that you want 3 values in the matrix: AmountDue,
> Fee, and Payments.
>
> For just the AmountDue, you would set up a crosstab with:
> - Year as Column Heading (group by)
> - Month as Row Heading (group by)
> - AmountDue as Value (sum)
>
> There is a technique for getting multiple value columns in a crosstab. See:
> http://allenbrowne.com/ser-67.html#MultipleValues
>
> Ultimately, you can then lay a report out the way you indicated based on
> those 3 values per record.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "magicdds" <magicdds(a)discussions.microsoft.com> wrote in message
> news:55483BD4-4D07-4B3C-B205-0E16618601B2(a)microsoft.com...
> >I have a query with the following results:
> >
> > Year Month AmountDue Fee Payments
> > 2007 1
> > 2007 2
> > 2007 3
> > 2007 4
> > 2007 5 $800.00
> > 2007 6 $1,080.00
> > 2007 7 $150.00
> > 2007 8 $350.00
> > 2007 9 $350.00
> > 2007 10 $350.00
> > 2007 11 $350.00
> > 2007 12 $350.00 $200.00
> > 2008 1 $150.00
> > 2008 2 $150.00 $200.00
> > 2008 3 $150.00
> > 2008 4 $150.00 $780.00
> > 2008 5 $5,835.00 $1,792.00 $1,295.00
> > 2008 6 $717.95 $52.95 $337.50
> > 2008 7
> > 2008 8
> > 2008 9
> > 2008 10
> > 2008 11
> > 2008 12
> >
> > I want to use this query to create a report that shows:
> >
> > Month 2007 2008
> >
> > 1 Amount Due 150
> > Fee
> > Payments
> > -------------------------------------------------
> > 2 Amount Due 150
> > Fee
> > Payments 200
> > -------------------------------------------------
> > 3 Amount Due 150
> > Fee
> > Payments
> > ------------------------------------------------
> > 4 Amount Due 150
> > Fee
> > Payments 780
> > ------------------------------------------------
> > 5 Amount Due 800 5835
> > Fee 1792
> > Payments 1295
> > ------------------------------------------------
> > 6 Amount Due 1080 717.95
> > Fee 52.95
> > Payments 337.50
> > ------------------------------------------------
> > 7 Amount Due 150
> > Fee
> > Payments
> > ------------------------------------------------
> > etc........
> >
> > But whatever I have tried, I get a report that prints out each month's
> > data,
> > twice. I also can't get the data into two columns, one for 2007 and a
> > second
> > column for 2008. I tried to make a 2007 and a 2008 subreport, which gave
> > me 2
> > columns but printed each month twice.
> >
> > Any ideas on how to accomplish the layout I'm looking for?
> >
> > Thanks,
> > Mark
> >
> >
> >
> >
> >
> >
>
>
 | 
Pages: 1
Prev: Reporting Out Two Names w/Phone # from the Same Table
Next: Show query Parameter in Chart?