Odbc ms query

[beancounter]

I am using ODBC to customer db. each customer has two services (water & sewer) the db breaks out the services onto two lines with an amount owed for each service. I need to create a flat file with the total amount owed.

Does anyone know how to group the customers and aggregate the amounts into one in ODBC before the exporting to Excel?

For instance - Current view:
Account # Current Balance Utility Type
1234 55.00 S
1234 45.00 W
5678 20.00 S
5678 20.00 W

Need:
Account # Current Balance Utility Type
1234 100.00
5678 40.00

Thanks ~ John

 

[RonB1111]

Just in case you're still looking for an answer...

This can be done within MS Query by clicking the SQL icon and then modifying the SQL statments as follows:
1. Add a GROUP BY statement at the bottom (but above the ORDER BY statement, if any). Then add the field names that you want the summary to be based on (ie: Account# only if you want the summary at account level)
2. In the SELECT statement, delete the "tablename.Utility Type" field because you won't be bringing this into excel and modify the "tablename.Account #" field to "Sum(tablename.Account #)" except don't use the quote marks.

If you have problems setting this up, please post your SQL and I'll try to modify it for you.