Prev: updating result in table
Next: Listing cases with no data as zero, and then calculating a tot
From: magicdds on 30 Jun 2008 00:34
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






From: Allen Browne on 30 Jun 2008 01:20
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: updating result in table
Next: Listing cases with no data as zero, and then calculating a tot