'Flattening' a table

[elsen...@yahoo.com]

I have a table with the following structure:

user_id
transaction_type
revenue_amt
items_sold

I have multiple rows for each user_id with a trans_type code and the
revenue they generated and number of items they sold per
transaction_type.

What I need to do is roll up the revenue for each user into one summary
row. I want to sum the revenue and items_sold for each of the different
trans_types into 2 buckets; 'full' and 'discount'. What I would end up
with is one row per user with the sum of revenue and sum of items for
'full' and 'discount' types:

user_id
full_revenue
discount_revenue
full_items_sold
discount_items_sold

I'm not very good with the IIf statement and I've been trying to use it
but end up with a column called 'Expr1' with 'Full' or 'Discount' in
it.

HELP!

Thanks!

[CDMAP...@fortunejames.com]

Here's my first try at it:

tblSales
SID Autonumber
user_id Long
transaction_type Text
revenue_amt Currency
items_sold Long

1 1 full $3,240.00 22
2 2 full $25 5
3 1 discount $2,534.00 17
4 1 other $18.00 4
5 1 discount $180 14

(Start Warmup)

qryItemsSoldByTransactionType:

SELECT user_id, SUM(items_sold) AS ItemsSold, transaction_type FROM
tblSales GROUP BY user_id, transaction_type;

!qryItemsSoldByTransactionType:
user_id ItemsSold transaction_type
1 31 discount
1 22 full
1 4 other
2 5 full

(End Warmup)

qryItemsSoldByTransactionType:
SELECT DISTINCT user_id, (SELECT SUM(A.items_sold) FROM tblSales AS A
WHERE A.transaction_type = 'full' AND A.user_id = tblSales.user_id) AS
FullItemsSold, (SELECT SUM(A.items_sold) FROM tblSales AS A WHERE
A.transaction_type = 'discount' AND A.user_id = tblSales.user_id) AS
DiscountItemsSold, (SELECT SUM(A.items_sold) FROM tblSales AS A WHERE
A.transaction_type <> 'full' AND A.transaction_type <> 'discount' AND
A.user_id = tblSales.user_id) AS OtherItemsSold FROM tblSales;

user_id FullItemsSold DiscountItemsSold OtherItemsSold
1 22 31 4
2 5 Null Null

James A. Fortune
CDMAP...@FortuneJames.com

[elsen...@yahoo.com]

So...it's not just a simple query on the table? I was hoping something
like the CASE statement in SQL. Thanks for the suggestion. I'll give it
a try.

[CDMAP...@fortunejames.com]

The one user_id per line is a snag. How about:

TRANSFORM Sum(items_sold) AS ItemSum SELECT user_id FROM tblSales GROUP
BY user_id PIVOT transaction_type;

That gave:

user_id discount full other
1 31 22 4
2 Null 5 Null

Note that 'other' is due to the transaction_type named 'other' rather
than to non-discount and non-full transaction_type's.

I'll take another look at it. I may have overlooked some simple way of
doing it.

James A. Fortune
CDMAP...@FortuneJames.com

[Jana]

You could accomplish the same thing by adding columns to your basic
select query using the IIF statement:

FullItemsSold:IIF([transaction_type] = "full",[items_sold],0)
FullRevenue: IIF([transaction_type] = "full",[revenue_amt],0)
DiscountItemsSold:IIF([transaction_type] = "discount",[items_sold],0)
DiscountRevenue:IIF([transaction_type] = "discount",[revenue_amt],0)

Run the select query to make sure that rows with discount items and
full items are showing up in the proper columns. If all looks good,
remove any columns except the user_id and the four new columns you made
above, and then change it to a totalling query, group by user_id and
sum the other columns.

Here's how the IIF statement works:
IIF(logical test, true value, false value)
So, for the FullItemsSold statement, it first looks at the
transaction_type field. If the value is "full", then it inserts the
value from the items_sold field. Otherwise, it inserts a zero. So any
transaction_type that is full will have a number in it from the record,
and anything else, it counts it as 0.

Clear as mud?

HTH,
Jana

[Randy Harris]

You haven't supplied enough information to build the full query, but
typically you would use something along the lines of:

SELECT user_id,
Sum(iif(transaction_type = 1, full_amt, 0)) as full_revenue,
Sum(iif(transaction_type = 2, full_amt, 0)) as discount_revenue,
Count(iif(transaction_type = 1, items_sold, 0)) as full_items_sold,
Count(iif(transaction_type = 2, items_sold, 0)) as discount_items_sold
FROM SalesTable
Group BY user_id

HTH

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[elsen...@yahoo.com]

Jana/Randy, THANK YOU!
Exactly what I was looking for.