Access said:
Thank you for the reply however my query was already set as you had advised.
My data is arranged as follows:
Customer Number, Customer Name, Revenue Code, Servicing District, Servicing
branch, date and amount.
My current query pulls the above data from one talbe and lists everything by
date.
I would like my query to list everything however, when it finds record that
have the same customer number, name, revenue code, servicing district,
servicing branch and date combine those records and add their amounts
togather (even if the amounts are different but everything else is the same)
That way I can see:
for xyz customer, for the month of april I had a total of x dollars.
My current method lists xyz maybe 4 or five times in a given month when the
only difference is just the amount. I hope this makes sense, and I hope you
can help. Thank you for your time.
If I understand you correctly, you will be able to list totals OR ELSE
all the records, but not both.
(Actually, you could do both, by combining the results of two Queries
using UNION, but I think it would make no sense for you to do that.)
So here's a suggestion:
[T_Transactions] is a Table containing transaction data:
Customer Customer Revenue Servicing Servicing date amount
Number Name Code District branch
-------- -------- ------- --------- --------- ----- ------
2 Jim 66s a c 9/13 $15.00
2 Jim 66s a c 9/13 $18.00
5 Mary xxx a c 9/10 $25.00
[Q_Transactions] is a Query defined this way:
SELECT T_Transactions.[Customer Number],
T_Transactions.[Customer Name],
T_Transactions.[Revenue Code],
T_Transactions.[Servicing District],
T_Transactions.[Servicing branch],
T_Transactions.date,
Sum(T_Transactions.amount) AS SumOfamount
FROM T_Transactions
GROUP BY T_Transactions.[Customer Number],
T_Transactions.[Customer Name],
T_Transactions.[Revenue Code],
T_Transactions.[Servicing District],
T_Transactions.[Servicing branch],
T_Transactions.date;
and when you run [Q_Transactions] you get this:
Customer Customer Revenue Servicing Servicing date SumOfamount
Number Name Code District branch
-------- -------- ------- --------- --------- ----- -----------
2 Jim 66s a c 9/13 $33.00
5 Mary xxx a c 9/10 $25.00
If you want to sum all the transactions for the whole month, not just a
day, then add a field to your Query that displays just the month and
year and use that, instead of the date.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
:
1. Create a query and add the table.
2. Insert the required field with whatever criteria.
3. Click the Total buttons from the Query Design toolbar.
4. Save and run.
--
Maha Aruppthan Pappan
Nacap Asia Pacific (Thailand) Co., Ltd.
:
I have a table with a lot of duplicate records ie; same customer name,
customer number, assigned to the same branch.
How can I run a query for a given month that will show me all of my
customers but when ever there is a duplacte record, it will combine them??
My data is as follows
customer number, Name, service branch, service company, revenue code,
amount. and date.
How can I combine customer records that have identical or duplacte above info?
