Date and payment query

[Robin Hood]

Can anyone help me, please!

I work for a charity and I am trying to find out how many people have donated within 1, 2, 3 and 4 months, from the start of the appeal date.

the appeal date field is [CAPPDATE_S]

the record of payment field is [CRECDATE]

Can anyone tell me how to create a query in order to find out how many people paid within the first 4 months, monthly?

Many thanks

 

[redneckgeek]

It just requires you to use the DATEADD and DATEDIFF functions:

SELECT Count(CRECDATE) AS DONATION, 
     DateDiff("m",[CAPPDATE_S], Now())+1 AS MNTH 
FROM [YOURTABLE]
WHERE CAPPDATE_S>=DateAdd("m",-3,Now())
GROUP BY DateDiff("m",CAPPDATE_S,NOW())+1

 

[Robin Hood]

It just requires you to use the DATEADD and DATEDIFF functions:

SELECT Count(CRECDATE) AS DONATION, 
     DateDiff("m",[CAPPDATE_S], Now())+1 AS MNTH 
FROM [YOURTABLE]
WHERE CAPPDATE_S>=DateAdd("m",-3,Now())
GROUP BY DateDiff("m",CAPPDATE_S,NOW())+1

Thanks for your quick reply, Redneckgeek!

Unfortunately I have limited knowledge of Access, and at the moment I am only doing simple queries, as I have no vb or sql experience.

Many thanks for your help

 

[Robin Hood]

Thanks for your quick reply, Redneckgeek!

Unfortunately I have limited knowledge of Access, and at the moment I am only doing simple queries, as I have no vb or sql experience.

Many thanks for your help

 

[MSAccessRookie]

Thanks for your quick reply, Redneckgeek!

Unfortunately I have limited knowledge of Access, and at the moment I am only doing simple queries, as I have no vb or sql experience.

Many thanks for your help

I think that this is about as simple as a query can get and still give you what you are looking for. Of course, you might still still want to associate it with a form or report that displays the results. How were you hoping to use it?

 

[Robin Hood]

Thanks for your reply, I am in no doubt the help from Redneckgeek will solve my problem. My inexperience in Access is what is letting me down, in how to utilise the code given to me, in order to achieve my aim. I have only ever used the 'Simple Query Wizard' in my queries.

What I am trying to achieve is as mentioned above in my cry for help. (forgeting to mention that all the data is coming from the COLLECT Table.)

Please excuse any lack of forum etiquette as today is the first time I have ever been involved in any kind of forum, and I am still trying to find my way around.

Any and all help is very much appreciated.

 

[redneckgeek]

It's very hard for us to tell you what to do in the query designer window.
All that designer does is convert your input into SQL. We can give you SQL, with the hopes that you can paste it into the designer on your own.

Go to a new, blank query (just opt out of the wizard). On your menu (File, Edit, View...) Go to View -> SQL View. That will bring up a blank white page. Paste in the SQL I've given above. Replace the part where
I've put in [yourtable] with the actual table name [collect] from your database.
Then go back to View->Design View, and it will bring up the more familiar GUI that you are used to.

 

[Robin Hood]

Thanks Redneckgeek,

All is well good now, your instructions were most helpfull.

cheers mate!

Robin Hood