Find Total Number of Payment Made in a Month Query (1 Viewer)

[JithuAccess]

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

 

[jdraw]

Can you post a copy of the database?

You could try
Month:Format([datPayment Date],"MMM-YY")

 

[plog]

You've got 2 issues--Month formatting and missing data. jdraw's code fixes the formatting issue. To fix the other issues you need a data source that covers the period you want to report on. You can't create records in a query if there are no records in the underlying datasource the query is built on.

Do you have a table that has every month/year you want to show in your query? If not, you should build one. It would look like this:

ReportMonths
ReportMonth
"Apr-20"
"May-20"
"Jun-20"
...


Then you can build a query using it and LEFT JOIN your other table to achieve what you want.

SQL LEFT JOIN Keyword

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[JithuAccess]

Can you post a copy of the database?

You could try
Month:Format([datPayment Date],"MMM-YY")

Hi,

Thanks a lot. Now I am getting like:

Two issues: I want to sort this data by Month. Like Apr-20, May-20, Jun-20...Mar-21 Second issue, though there isn't any Record for May I want to show like

Month Total
----------- -------
May-20 0

I am attaching the Database.

Thanks so much for your help.

 

[arnelgp]

see Query3.

 

[JithuAccess]

see Query3.

Thanks a lot