Thread: summing balance for customers

Hi there got a query that has about 1000 records... but is made up about 400 customers. I would like to add the balance of each customer if possible... ex:

Coulmn A Column B
SSN Balance

222-22-2222 $500.00
111-11-1111 $600.00
222-22-2222 $1,000.00
222-22-2222 $300.00

so the end result would be:

Coulmn A Column B Column C
SSN Balance Total Balance
222-22-2222 $500.00 $1,800.00
111-11-1111 $600.00 $600.00
222-22-2222 $1,000.00 $1,800.00
222-22-2222 $300.00 $1,800.00


Anyone know how to do this??? I know I can create a report to do this but I'd like it in a query...

SELECT SSN, Balance, SUM(Balance) as Total_Balance FROM tablename GROUP BY SSN

Actually, yes, that is what Dave means. He's given you the SQL version of a sample query that will provide what you've asked. (Except for the "tablename" of course.)

Have a look here to see how to get to the SQL in the Access Query Designer.

Cheers,

A possible cause would be in the different spelling for your table name:

MRT_D_ACCOUNTS; vs,
MRT_F_ACCOUNTS.

They should be the same.

If you pull data from two tables at once, you need to list them both in the FROM clause, and you need to specify the tables' relationship to each other (the JOIN).

Have a look at the tutorials and instructionals at W3C, they will help you understand who to construct SQL statements.

You need to add a WHERE clause

SELECT [ARMS C&R I].SSN_TIN, Sum([ARMS C&R I].CURRENT_BALANCE) AS SumOfCURRENT_BALANCE
FROM [ARMS C&R I]
GROUP BY [ARMS C&R I].SSN_TIN
WHERE [ARMS C&R I].SSN_TIN <> 000000000 and [ARMS C&R I].SSN_TIN <>111111111 and [ARMS C&R I].SSN_TIN <>999999999;

I get a syntax error (missing operator) in query expression

alansidman's SQL looks pretty good to me. What type of data is your SSN field? Number?

From the forum rules

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.