Grouping Sum by Month (1 Viewer)

[JithuAccess]

Hello Guys,

I was trying to Group Payment Amount with Payment Date.

This is my Query:

and This is my Query Result:

As you could see, for July, the Total Payment was $221,260.00 and $22,126.00 and this is showing Separately in the Query. I think if there are different Dates, Query is grouping the Paid Amount by Dates. I want to show this Monthly. Like For July 2020 the Total Paid Amount $243,386.00.

Could you please let me know how to do this?

Thanks

 

[bob fitz]

Perhaps:
Change the first field to Mth:Month([NameOfDateField])

 

[Minty]

I'll add to Bob's excellent advice - add a year column and group on that well; otherwise, it will group all July's forever.

repYear: Year(NameOfDateField])
repMonth : Month([NameOfDateField])

 

[bob fitz]

I'll add to Bob's excellent advice - add a year column and group on that well; otherwise, it will group all July's forever.

repYear: Year(NameOfDateField])
repMonth : Month([NameOfDateField])

Ooops. As usual, your excellent advice is even more excellent

 

[Minty]

To be fair @bob fitz , I've fallen for that one on far too many occasions.

Hmm, those numbers look a bit big for January's sales...

 

[JithuAccess]

Ooops. As usual, your excellent advice is even more excellent

Thanks a lot. It worked Perfectly.

Got a small issue. There isn't any Payment made for May-2020 so in the Query Result it is not showing. But we need to show this in our Report as May-2020 $0.00

Thanks

 

[Minty]

That's actually quite awkward to overcome.

You can't group on data that doesn't exist (obviously), so you would need to bring something in that does have that date in it or create something that you can left join your results to, any table you have with guaranteed Calendar dates you need?

If you don't have one this can be where a Calendar Table is useful.

 

[JithuAccess]

That's actually quite awkward to overcome.

You can't group on data that doesn't exist (obviously), so you would need to bring something in that does have that date in it or create something that you can left join your results to, any table you have with guaranteed Calendar dates you need?

If you don't have one this can be where a Calendar Table is useful.

Thanks a lot and Sorry for asking too much Stupid Questions.

 

[plog]

The same solution offered in this nearly identical thread you asked 2 days ago can be used for this:

Find Total Number of Payment Made in a Month Query

Hello Guys, In my Table I have a field Payment Date. I want to find the Count of Payments made in a month. This is the data in my Table: I want to display like this in my Query: This is my Query: And this is my Query Result: Could you please let me know how to Fix this? Thanks

www.access-programmers.co.uk