criteria

[maya]

hallo all,

i have table Transaction(ID,Date,Costumer,product
number,Sale amount). Now i want to make query from that
table base on this criteria;
exclude ONLY the transactions if the total of it BY same
costumer,same product number have Sale amount > $50k

im trying to do it by using crosstab query as Product
number using row heading, Costumer using column heading
and Sale amount using value. later on i sum up all sale
amount on its product number row.


TRANSFORM Sum(Transaction.[Sale Amount]) AS [SumOfSale
Amount]
SELECT Transaction.[Costumer]
FROM [Transaction]
GROUP BY Transaction.[Costumer]
PIVOT Transaction.[Product number];

now how do i exclude it from original table?or is that
any shorter way to deal with it?how would my query be?
please help

THT,
maya

 

[Gary Walter]

Hi maya,

I am not sure what you want (nor why),
but this may be what you want:

TRANSFORM Sum(t1.[Sale amount]) AS S
SELECT t1.Customer
FROM [Transaction] As t1
INNER JOIN
(SELECT t.Customer, t.[Product number]
FROM [Transaction] As t
GROUP BY t.Customer, t.[Product number]
HAVING Sum(t.[Sale amount])<=50000) As x
ON
t1.Customer=x.Customer
AND
t1.[Product number]=x.[Product number]
GROUP BY t1.Customer
PIVOT t1.[Product number];

This would still show the "Customer row"
if at least one of the that Customers "product sum"
was <= 50000...it would just show a "blank"
in the product column for that Customer
where the "sum" was > 50000.

If you don't want to show a Customer row *at all*
if that Customer had *any* "product sum" >50000,
then please respond back.

Good luck,

Gary Walter