Breaking up one field into 2 columns and 2 rows

[Matt Williamson]

I have a table with 3 columns Totals, SalesOffice and Type. Type contains 4
different entries Cash Contribution, Cash Distribution, Security
Contribution and Security Distribution. I want to set the report up like
this:
SalesOffice Contributions Distributions Net
Cash
Securities

I can't figure out how to get it to display correctly. I've tried using 4
labels with
=IIF([Type]="Cash Contribution","[Totals],"")
=IIF([Type]="Cash Distribution","[Totals],"")

etc but it's just showing #Error when I run the report. Any insight would be
highly appreciated. I wrote a stored proc on a SQL 2005 server to create the
output table so I can modify it if needed to make this easier. I'm working
with Access 2003.

TIA

-Matt

 

[Duane Hookom]

Your "I want to set the report up like this" only shows what I think are
labels. Can you share some actual data and do a better job of how these
should appear on the report?

Both of your IIf() expressions have "[Totals] where the quote is
certainly not needed. Also, IIf() should be written to return a consistent
data type. I expect [Totals] is numeric while "" is text. IMO, this is
wrong. I would replace "" with either Null or 0.

 

[Matt Williamson]

Your "I want to set the report up like this" only shows what I think are
labels. Can you share some actual data and do a better job of how these
should appear on the report?

Both of your IIf() expressions have "[Totals] where the quote is
certainly not needed. Also, IIf() should be written to return a consistent
data type. I expect [Totals] is numeric while "" is text. IMO, this is
wrong. I would replace "" with either Null or 0.

Sure. Here is a sample of the data

Totals salesoffice type
774001 200 Cash Contribution
1778542.48 200 Cash Distribution
1438952.08 200 Security Contribution
2004847.06 200 Security Distribution
2553564.31 300 Cash Contribution
4134069.93 300 Cash Distribution
2603129.57 300 Security Contribution
3117264.46 300 Security Distribution

Here is how I want that to display on the report

Salesoffice Contribution Distribution Net

200 Cash $774,001 $1,778,542 ($1,004,541)
Security $1,438,952 $2,004,847 ( $565,895)

300 Cash $2,553,564 $4,134,069 ($1,580,505)
Security $2,603,129 $3,117,264 ( $514,135)


____________________________________________________

Total Cash $3,327,565 $5,912,611 ($2,585,046)
Total Security $4,042,081 $5,122,111 ($1,080,030)


TIA

Matt

 

[John Spencer]

The SQL for your query Could look like the following.

SELECT SalesOffice
, SUM(IIF([Type]='Cash Contribution',Totals,Null)) as Contribution
, SUM(IIF([Type]='Security Contribution',Totals,Null)) as Distribution
, SUM(IIF([Type]='Cash Contribution',Totals,Null))-
SUM(IIF([Type]='Security Contribution',Totals,Null)) As [Net Cash]
....
FROM [YOUR TABLE]
GROUP BY SalesOffice

BUT Easier yet would be a crosstab query used as the source of your report.
You could do the math in the report

TRANSFORM Sum(Totals) as Amount
SELECT SalesOffice
FROM [Your Table]
GROUP BY SalesOffice
PIVOT [Type] IN ('Cash Distribution','Cash Contribution','Security
Distribution','Security Contribution')

That query should return 6 fields.

To build the crosstab query in query design view
== Add your table
== Select the fields
== Select Query: Crosstab from the menu
== Under Totals change Group by to Sum and Select Value as the crosstab value
== Under Type select Column Heading as the crosstab value
== Under SalesOffice select Row Heading as the crosstab value


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Your "I want to set the report up like this" only shows what I think are
labels. Can you share some actual data and do a better job of how these
should appear on the report?

Both of your IIf() expressions have "[Totals] where the quote is
certainly not needed. Also, IIf() should be written to return a consistent
data type. I expect [Totals] is numeric while "" is text. IMO, this is
wrong. I would replace "" with either Null or 0.

Sure. Here is a sample of the data

Totals salesoffice type
774001 200 Cash Contribution
1778542.48 200 Cash Distribution
1438952.08 200 Security Contribution
2004847.06 200 Security Distribution
2553564.31 300 Cash Contribution
4134069.93 300 Cash Distribution
2603129.57 300 Security Contribution
3117264.46 300 Security Distribution

Here is how I want that to display on the report

Salesoffice Contribution Distribution Net

200 Cash $774,001 $1,778,542 ($1,004,541)
Security $1,438,952 $2,004,847 ( $565,895)

300 Cash $2,553,564 $4,134,069 ($1,580,505)
Security $2,603,129 $3,117,264 ( $514,135)


____________________________________________________

Total Cash $3,327,565 $5,912,611 ($2,585,046)
Total Security $4,042,081 $5,122,111 ($1,080,030)


TIA

Matt

 

[Matt Williamson]

BUT Easier yet would be a crosstab query used as the source of your
report.
You could do the math in the report

TRANSFORM Sum(Totals) as Amount
SELECT SalesOffice
FROM [Your Table]
GROUP BY SalesOffice
PIVOT [Type] IN ('Cash Distribution','Cash Contribution','Security
Distribution','Security Contribution')

That query should return 6 fields.

To build the crosstab query in query design view
== Add your table
== Select the fields
== Select Query: Crosstab from the menu
== Under Totals change Group by to Sum and Select Value as the crosstab
value
== Under Type select Column Heading as the crosstab value
== Under SalesOffice select Row Heading as the crosstab value

Thanks John the crosstab query worked great. The only issue I'm having now
is getting a total field in the page footer to work. I've set the running
sum property in each of the Cash and Security contribution and distribution
text boxes to Over All and added text boxes with =SUM([Cash Contributions]),
etc in the Page footer but it's just giving me #Error when I run the
report. Any idea what might cause that?

TIA

Matt

 

[Duane Hookom]

Aggregate expressions like =Sum() or =Count() don't work in the Page Footer.
Place them in either a Group or the Report Footer.

--
Duane Hookom
MS Access MVP

BUT Easier yet would be a crosstab query used as the source of your
report.
You could do the math in the report

TRANSFORM Sum(Totals) as Amount
SELECT SalesOffice
FROM [Your Table]
GROUP BY SalesOffice
PIVOT [Type] IN ('Cash Distribution','Cash Contribution','Security
Distribution','Security Contribution')

That query should return 6 fields.

To build the crosstab query in query design view
== Add your table
== Select the fields
== Select Query: Crosstab from the menu
== Under Totals change Group by to Sum and Select Value as the crosstab
value
== Under Type select Column Heading as the crosstab value
== Under SalesOffice select Row Heading as the crosstab value

Thanks John the crosstab query worked great. The only issue I'm having now
is getting a total field in the page footer to work. I've set the running
sum property in each of the Cash and Security contribution and
distribution text boxes to Over All and added text boxes with =SUM([Cash
Contributions]), etc in the Page footer but it's just giving me #Error
when I run the report. Any idea what might cause that?

TIA

Matt