Count of Transaction Types

H

Haji

Hi,

I have a table called InvoiceDetail that has
TransactionNumber, TransactionType, and Amount. We have
two transaction types: Products and Services. Sometimes
a customer will come and purchase a product or products
only, sometimes they will purchased a product or products
and a service or services and sometimes they will only
have a service or services without products. If they
purchase more than one thing, the transaction number will
remain the same. So the transactions for two different
customer might look like this:

TransactionNumber TransactionType Amount ItemNumber
100 S $100 3456
100 P $50 5678
100 P $25 7890
200 S $100 2345
300 P $75 1234

What I want to do is to calculate how many transactions
were product only, how many were service only and how
many were both. It would also be nice to know amounts as
well. For product only transactions, what was the
average ticket amount. For service only what was the
average ticket amount. And for product and service -
what was the average ticket amount for each (i.e. ave
ticket amount was $78 for services and $55 for products).

Can anyone help?

Thanks,

Haji

..
 
T

Tom

Hi,
I`m not sure what are your needs, but try this:
Create 3 queries with criteria on the same table.
one as service that will filter all the services, second with products to
filter all the products, and a third with no filter.
for each query add group by and put count - you will get how many record you
have.
from these create reports with any operation.


Tom
 
Top