SUMMARIZING RECORDS ON A TABLE

A

ANGELICA

Hey!
I have a customer database, describing monthly bank transactions for each
one of them.
Now, what I need is to select those customers whit higher volume of
transactions, in other words, summarize number of transactions per customer
and total amount of money moved.
Example:†I want my database to show me who moved more than $50,000 and in
how many transactions.â€
I already try queries, and reports but none of them works as I want..
Would that be to much asking?.... by the way, any answer, excel or access
will work!
Thanks for helping!!!!
 
S

Sprinks

Hi, Angelica.

I always use a divide and conquer approach in these situations, where you
want to filter by a sum, but want all the detail.

First, find the customers who exceed 50,000 total transactions with a Totals
query. You’ll need only their CustomerID and, optionally, the sum, although
this can be calculated in the group footer of your report:

' With Sum
SELECT Transactions.CustomerID, Sum(Transactions.Amount) AS SumOfAmount
FROM Transactions
GROUP BY Transactions.CustomerID
HAVING (((Sum(Transactions.Amount))>50000));

Or

' Without sum
SELECT Transactions.CustomerID
FROM Transactions
GROUP BY Transactions.CustomerID
HAVING (((Sum(Transactions.Amount))>50000));

Then create a second query that joins the new query by CustomerID to the
Transactions table to get the detail on each transaction. I’ve included an
Amount field and a TransactionDate field. You might also want a Transaction
Number, or Transaction Type, etc. The query is also joined to the Customers
table to get the Customer Name for the report. The Totals query was named
qryLargeTransactions.

The net effect of the 2nd query is to produce the CustomerID, CustomerName,
Transaction Amount, and Transaction Date for all customers who have a total
transaction amount of > $50,000.

SELECT qryLargeTransactions.[CustomerID], [Customers].[CustomerName],
[Transactions].Amount, [Transactions].[TransactionDate]
FROM Transactions INNER JOIN (qryLargeTransactions INNER JOIN Customers ON
qryLargeTransactions.[CustomerID]=[Customers].[CustomerID]) ON
[Transactions].[CustomerID]=[Customers].[CustomerID];

Hope that helps.
Sprinks
 
Top