How to sum the same account with multiple transactions (1 Viewer)

[smyck]

Hello, I am in need of some help with designing this query. I need to sum all the transaction amounts according to their account ID so that I only have one line for each account ID showing the total from all the transactions. This is what it looks like in SQL view right now:
SELECT DISTINCTROW ChartOfAccounts.[Account ID], ChartOfAccounts.[Account Name], ChartOfAccounts.[Account Type ID], ChartOfAccounts.Statement, ChartOfAccounts.[Balance Sheet Heading], Transactions.[Tx Amount] AS [Sum Of Tx Amount]
FROM ChartOfAccounts INNER JOIN Transactions ON ChartOfAccounts.[Account ID] = Transactions.[Account ID]
WHERE (((ChartOfAccounts.[Account Type ID])=1 Or (ChartOfAccounts.[Account Type ID])=2 Or (ChartOfAccounts.[Account Type ID])=3));

I also attached a picture of what my query currently looks like.

I honestly do not have much experience with access or SQL so simple instructions would be really appreciated.

 

[Ranman256]

you only need 2 fields in the query
AcctID, Sum(TxAmt)

if you a limiting the sum based on ChartOfAcct,
bring the field down but uncheck the SHOW checkbox. Its a filter but wont show.

 

[smyck]

you only need 2 fields in the query
AcctID, Sum(TxAmt)

if you a limiting the sum based on ChartOfAcct,
bring the field down but uncheck the SHOW checkbox. Its a filter but wont show.

I need the other information to build a report later from this query. When I enter in:
SELECT DISTINCTROW ChartOfAccounts.[Account ID,sum(TxAmount)]
It asks for a parameter value.... Is this how SQL view should look or am I doing something wrong?

 

[HiTechCoach]

I need the other information to build a report later from this query. When I enter in:
SELECT DISTINCTROW ChartOfAccounts.[Account ID,sum(TxAmount)]
It asks for a parameter value.... Is this how SQL view should look or am I doing something wrong?

The [ and ] are causing Access to see it as a parameter

It should look something like:

SELECT DISTINCTROW ChartOfAccounts.[Account ID] ,sum(TxAmount)

Using spaces in field names sure make it difficult to code.

I agree with Ranman256 that you should use a totaling query and Group By [Account ID] instead of DISTINCTROW

 

[smyck]

The [ and ] are causing Access to see it as a parameter

It should look something like:

SELECT DISTINCTROW ChartOfAccounts.[Account ID] ,sum(TxAmount)

Using spaces in field names sure make it difficult to code.

I agree with Ranman256 that you should use a totaling query and Group By [Account ID] instead of DISTINCTROW

So I used the Query Wizard to construct this query, so how would I change from DISTINCTROW to a totaling query and group by [account ID]?
I changed SQL to this:
SELECT DISTINCTROW ChartOfAccounts.[Account ID], ChartOfAccounts.[Account Name], ChartOfAccounts.[Account Type ID], ChartOfAccounts.Statement, ChartOfAccounts.[Income Statement Heading], ChartOfAccounts.[Balance Sheet Heading], Sum(Transactions.[Tx Amount]) AS [Sum Of Tx Amount]
and it won't accept it, saying there is a syntax error (missing operator) in query expression 'sum(Tx Amount)'

** EDIT: I figured it out! Thank you for your help!

 

[HiTechCoach]

You're welcome.

Thanks for the update.

Glad we could assist.