🚀 go-pugleaf

Brad,

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

Brad <brad@xspace.com> wrote in message
news:rvft69ktmom67@corp.supernews.com...
> Hmm. I thought this would be easy.
>
> 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.
> If you have any suggestions, please reply directly to brad@xspace.com
>
> Thanks!
>
>
>