Access2007 Append Query To Sum Related Records In A Field To Another Table

[MarkGardner]

ACCESS2007
I am trying to sum fields and group related records together (as in a report, but in an append query) so that I can place the resulting summarized data into another table. The sum functions get me field totals, but I cannot find anything that will allow me to total by groupings in a query. I am looking for something similar to the report groupings that work so well.

I have a field called STMTCODE that I want to group together all records with the same stmtcode. Then I want to sum all fields with the same STMTCODE into a single record in a different table.

Thanks to anyone that can assist.

Mark Gardner

 

[jal]

ACCESS2007
I am trying to sum fields and group related records together (as in a report, but in an append query) so that I can place the resulting summarized data into another table. The sum functions get me field totals, but I cannot find anything that will allow me to total by groupings in a query. I am looking for something similar to the report groupings that work so well.

I have a field called STMTCODE that I want to group together all records with the same stmtcode. Then I want to sum all fields with the same STMTCODE into a single record in a different table.

Thanks to anyone that can assist.

Mark Gardner

I don't see why you feel that the Sum function doesn't handle groupings. It was my assumption that it does.

SELECT STMTCODE , SUM(STMTCODE)
INTO Table2
FROM Table1
GROUP BY STMTCode

Any reason that won't work?

 

[MarkGardner]

Thanks for your input, jal. I am sure that it will work, but I am a little dense and do not quite understand how to place the instructions. Can you help me to understand your suggested setup a little better. What I have set up in my append query already is:

Field: Sum of POOLCROOLCR
Table: STMTS
Total: Group by
Sort:
Append to: PoolCR
Criteria:
or:

(note to above setup... ACCESS will not allow me to put anything after "Group by" and Append to PoolCr is in STMT SUMMARY table). Now where does your suggested setup go int the query? I hope that it is not VBA, because I struggle with VBA more.
Thanks.

 

[jal]

Thanks for your input, jal. I am sure that it will work, but I am a little dense and do not quite understand how to place the instructions. Can you help me to understand your suggested setup a little better. What I have set up in my append query already is:

Field: Sum of POOLCROOLCR
Table: STMTS
Total: Group by
Sort:
Append to: PoolCR
Criteria:
or:

(note to above setup... ACCESS will not allow me to put anything after "Group by" and Append to PoolCr is in STMT SUMMARY table). Now where does your suggested setup go int the query? I hope that it is not VBA, because I struggle with VBA more.
Thanks.

I was speaking of pasting into sql view. I never learned how to use the query grid because it seemed to me that learning it requires just as much time and effort as learning sql, because it's a bit restrictive so it sometimes forces you to think harder and work harder (with pure sql you are free to put subqueries all over the place which can be very convenient).

AnyWho, I think the general idea for your append query is, in your first grid column, setup a group by for StmtCode field

Field: StmtCode
Total: Group By
Append to PoolCR

And then for each field that you want to sum (let's assume you have a field called Price) do this:

Field : Price
Total: Sum
Append to WHATEVER

where " Whatever " is the appropriate column in your destination table.

If I got this right, this will be my first-ever successful use of the query grid.