Sum based on repeating field (1 Viewer)

[HotelierDream]

I am going to try and make this make sense but I cannot promise it will....

I have a table that has transactions on it, there are fields Month, TransCode, TransDesc, Qty, GrossAmt.

The TransCode,TransDesc and Month fields repeat as it is just a list of all entries for the month.

I want to make a query that will take the items and summarize them and amend the entry to a new table...

In example

Month	TransCode	TransDesc	QTY	GrossAMT
December	10000	Food	5	500
December	10000	Food	4	400
December	10000	Food	2	200

Would become: (On the other table)

Month	TransCode	TransDesc	QTY	GrossAMT
December	10000	Food	11	1100

 

[theDBguy]

Hi. Welcome to AWF!

You could try using a Totals query. For example,

SELECT [Month], TransCode, TransDesc, Sum(Qty), Sum(GrossAMT)
FROM TableName
GROUP BY [Month], TransCode, TransDesc

 

[HotelierDream]

I could swear I tried it earlier and It didnt work.... now it did. Access is mocking me! LOL

Thank you so much!

 

[theDBguy]

I could swear I tried it earlier and It didnt work.... now it did. Access is mocking me! LOL

Thank you so much!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[The_Doc_Man]

The only thing you said that was perhaps not quite right was this: If instead of "Month" you had the actual date, you could skip updating another table with what is essentially duplicated data. You could have the query look at your date field and use the DatePart functions to build whatever kind of date information you needed. Which would include doing your summary/rollup for any month on record.

 

[HotelierDream]

Hi. Glad to hear you got it sorted out. Good luck with your project.

Thank you!

 

[Pat Hartman]

I realize your question was answered but the solution is incorrect.
1. If your transactions are really marked only by month, you're going to have a big problem come January.
2. It is not necessary to save the summarized data to a new table. What are you going to do next month? Any time you want the summary, just run a query.