Very Computer

In Microsoft Access I have a set of data that looks like this:

AccountNum        Amount
2000A                    12
2000B                    3
4000A                    50
4000B                    40

I would like to 'add-up' accounts of the same number (eg: combine the "A"
accounts with the "B" accounts) in an SQL query. For example, the resulting
table would look like:

NewAccountNum        NewAmount
2000                            15
4000                             90

I've been trying to use a sub query that goes seomthing like:

SELECT
Amount +
    (SELECT Amount FROM table WHERE AccountNum = Left
    (AccountNum,LEN(AccountNum)-1) & "B")

AS NewAmount

FROM table

WHERE RIGHT(AccountNum,1) <> "B"

But of course my sub-query returns multple values.

Thanks!

You might try something like:

select substring(AccountNum, 1, datalength(AccountNum) - 1),
       sum(Amount)
  from YourTable
 group by substring(AccountNum, 1, datalength(AccountNum) - 1)

(BTW:  The substring might be overkill, but I wasn't sure how your
AccountNum data was structured.)

Hope this helps!

John Peterson