Need to create totals using a Crosstab Query

  • Thread starter Thread starter DALLASDAN
  • Start date Start date
D

DALLASDAN

I have a table that contains transaction records. I need to summarize
revenue of transactions by transactions source. I can do this creating a
Report from a Crosstab Query which works fine. Now each Transaction is
either a "Buy" or "Sale" transaction. Now I would like to summerize each
transaction source but have the revenue totals broken out for "Buy" and
"Sale".

Sample Input data:
SOURCE/ TRANS TYPE / REVENUE
phone / buy / 1000
phone / sale / 500
newspaper / buy / 300
newpaper / buy / 500
newpaper /sale / 100

Desired Report:
SOURCE / # TRANSACTIONS / BUY REVENUE / SALE REVENUE
Phone / 2 / 1000 / 500
Newspaper / 3 / 800 / 100
 
You don't need a crosstab query for this.

SELECT Source
, Count(Source) as [# Transactions]
, Sum(IIF([Trans Type] = "Buy",Revenue,0)) as [Buy Revenue]
, Sum(IIF([Trans Type] = "Sale",Revenue,0)) as [Sale Revenue]
FROM [Your Table]
GROUP BY Source

In query design view
== Add your table
== Add the Source field twice and add the Revenue field twice
== Select View: Totals from the menu
== Change Group By to Count under the second source field
== Change Group by to Expression under both revenue fields
== Edit the first Revenue field so it looks like
[Buy Revenue]: Sum(IIF([Trans Type] = "Buy",[Revenue],0))
== Edit the second Revenue field so it looks like
[Buy Revenue]: Sum(IIF([Trans Type] = "Sale",[Revenue],0))

Save the query

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
John, Appreciate the response, I am fairly green at Acess, Should I put the
[Buy Revenue]: Sum(IIF([Trans Type] = "Buy",[Revenue],0)) in the filed name?

DD

John Spencer said:
You don't need a crosstab query for this.

SELECT Source
, Count(Source) as [# Transactions]
, Sum(IIF([Trans Type] = "Buy",Revenue,0)) as [Buy Revenue]
, Sum(IIF([Trans Type] = "Sale",Revenue,0)) as [Sale Revenue]
FROM [Your Table]
GROUP BY Source

In query design view
== Add your table
== Add the Source field twice and add the Revenue field twice
== Select View: Totals from the menu
== Change Group By to Count under the second source field
== Change Group by to Expression under both revenue fields
== Edit the first Revenue field so it looks like
[Buy Revenue]: Sum(IIF([Trans Type] = "Buy",[Revenue],0))
== Edit the second Revenue field so it looks like
[Buy Revenue]: Sum(IIF([Trans Type] = "Sale",[Revenue],0))

Save the query

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a table that contains transaction records. I need to summarize
revenue of transactions by transactions source. I can do this creating a
Report from a Crosstab Query which works fine. Now each Transaction is
either a "Buy" or "Sale" transaction. Now I would like to summerize each
transaction source but have the revenue totals broken out for "Buy" and
"Sale".

Sample Input data:
SOURCE/ TRANS TYPE / REVENUE
phone / buy / 1000
phone / sale / 500
newspaper / buy / 300
newpaper / buy / 500
newpaper /sale / 100

Desired Report:
SOURCE / # TRANSACTIONS / BUY REVENUE / SALE REVENUE
Phone / 2 / 1000 / 500
Newspaper / 3 / 800 / 100
Click to expand...
.
Click to expand...
 
John,

I think you got me where I need to be.

John Spencer said:
You don't need a crosstab query for this.
Click to expand...
SELECT Source
, Count(Source) as [# Transactions]
, Sum(IIF([Trans Type] = "Buy",Revenue,0)) as [Buy Revenue]
, Sum(IIF([Trans Type] = "Sale",Revenue,0)) as [Sale Revenue]
FROM [Your Table]
GROUP BY Source

In query design view
== Add your table
== Add the Source field twice and add the Revenue field twice
== Select View: Totals from the menu
== Change Group By to Count under the second source field
== Change Group by to Expression under both revenue fields
== Edit the first Revenue field so it looks like
[Buy Revenue]: Sum(IIF([Trans Type] = "Buy",[Revenue],0))
== Edit the second Revenue field so it looks like
[Buy Revenue]: Sum(IIF([Trans Type] = "Sale",[Revenue],0))

Save the query

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a table that contains transaction records. I need to summarize
revenue of transactions by transactions source. I can do this creating a
Report from a Crosstab Query which works fine. Now each Transaction is
either a "Buy" or "Sale" transaction. Now I would like to summerize each
transaction source but have the revenue totals broken out for "Buy" and
"Sale".

Sample Input data:
SOURCE/ TRANS TYPE / REVENUE
phone / buy / 1000
phone / sale / 500
newspaper / buy / 300
newpaper / buy / 500
newpaper /sale / 100

Desired Report:
SOURCE / # TRANSACTIONS / BUY REVENUE / SALE REVENUE
Phone / 2 / 1000 / 500
Newspaper / 3 / 800 / 100
Click to expand...
.
Click to expand...
 
Back
Top