querry, to add amounts per months

[benjamin.grimm]

Hello guys,

i´ve got a table with a lot of data.

In the table is a SAP Number (ID), a booking date (BuchDatum) and an amount (Betrag)

Now i want to sum up, for each ID the amount in one months.

In the end i want to have a table like this

Date SAP Number Amount
01/2011 12345 1000€
02/2011 12345 0€
03/2011 12345 100€
04/2011 12345 300€

and so on.

So for each ID, a table like this.

I tried it allready, but my Problem is that i either sum up the years or the months. I Need a combination.

I put the file in the Appendix

greetz benjamin

 

[plog]

First, calculated values shouldn't be in a table, they should be calculated when you need them. In this instance you would use a query. Below is the SQL for that query:

SELECT Month([BuchDatum]) & "/" & Year([BuchDatum]) AS DateGrouping, tbl_SAP.SAP_Nummer, Sum(tbl_SAP.BetragSAP) AS SumOfBetragSAP
FROM tbl_SAP
GROUP BY Month([BuchDatum]) & "/" & Year([BuchDatum]), tbl_SAP.SAP_Nummer;