Need to create totals using a Crosstab Query

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
 
J

John Spencer

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
 
D

DALLASDAN

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...
 
D

DALLASDAN

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query - Recalculate totals after each date 2
Crosstab Query horizontal totals 1
Need to create different files based on subtotal 1
How can point me out the best way on a query 7
Can a crosstab query perform a running total? 3
Pass a Value to a CrossTab Query 6
need help making a query (crosstab instead of Pivot tables?) 2
Need query to separate 2 entry types in a table field 31

Top