Current Month and Previous Month

J

John Doe

Hi. I'm trying to set up a query which will return the
results from the current month and another query which
will return the results from the previous month.

I have a field called Service_date in which the data for
that particular date is recorded.

The current month would mean all the data with
service_date between March 1st uptil March 15th (today).

The previous month would mean all the data with
service_date between February 1st to February 28th (29th
this year).

How to write these queries? Please explain.
 
N

Nikos Yannacopoulos

John Doe,

In your query design add two calculated fields and use the Month() and
Year() function on your date field, to return the month and year. Then in
the criteria line use the same functions on the Date function (current
date), so the query will only return the current month's records.
Now for the previous month you would do something similar
(Month(Date()) -1), only you need to take into account year changes. So, in
the month field you should use criterion:
Iif(Month(Date())=1,12,Month(Date())-1)

Likewise, in the year field:
Iif(Month(Date())=1,Year(Date())-1,Year(Date()))

HTH,
Nikos
 
J

John Doe

Well, I got the current month working by simply using Month
(service_date))=Month(Now())

I need that sort of simple technique to get previous
months data as well.
 
J

John Doe

Would Month(Service_date)=Month(Now()) work for Current
Month
and Month(Service_date)=Month(Now()-1) work for previous
month?

Please help
 
J

John Vinson

Would Month(Service_date)=Month(Now()) work for Current
Month
and Month(Service_date)=Month(Now()-1) work for previous
month?
Click to expand...

Not quite: it would get you the results for March 2004, March 2003,
March 2002, etc. - any data where *just the month* matches.

The most efficient way (which takes advantage of an Index on the
Service_Date field) would be:

Current month -

[ServiceDate] > DateSerial(Year(Date()), Month(Date()), 1) AND <
DateSerial(Year(Date()), Month(Date()) + 1, 1)

Previous month -

[ServiceDate] > DateSerial(Year(Date()), Month(Date()) - 1, 1) AND <
DateSerial(Year(Date()), Month(Date()), 1)
 
G

Guest

SELECT Sum(Patients.new_patients) AS [Sum Of
new_patients], Sum(Patients.established_patients) AS [Sum
Of established_patients], Sum(Patients.total_patients) AS
[Sum Of total_patients], Sum(Patients.angiograms) AS [Sum
Of angiograms], Sum(Patients.fundus) AS [Sum Of fundus],
Sum(Patients.ascan) AS [Sum Of ascan], Sum(Patients.bscan)
AS [Sum Of bscan], Sum(Patients.cataracts) AS [Sum Of
cataracts], Sum(Patients.retinal) AS [Sum Of retinal],
First(Patients.log_date) AS [First Of log_date], First
(Patients.service_date) AS [First Of service_date] FROM
Patients WHERE Patients.service_date > DateSerial(Year(Date
()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month
(Date()) + 1, 1);

This doesnt work for current month :(

-----Original Message-----
Would Month(Service_date)=Month(Now()) work for Current
Month
and Month(Service_date)=Month(Now()-1) work for previous
month?
Click to expand...

Not quite: it would get you the results for March 2004, March 2003,
March 2002, etc. - any data where *just the month* matches.

The most efficient way (which takes advantage of an Index on the
Service_Date field) would be:

Current month -

[ServiceDate] > DateSerial(Year(Date()), Month(Date()), 1) AND <
DateSerial(Year(Date()), Month(Date()) + 1, 1)

Previous month -

[ServiceDate] > DateSerial(Year(Date()), Month(Date()) - 1, 1) AND <
DateSerial(Year(Date()), Month(Date()), 1)




.
Click to expand...
 
J

John Spencer (MVP)

It doesn't work because you have an error in your WHERE clause. You need to
list the service_date field for each criteria clause; you only listed it once.

SELECT
Sum(new_patients) AS [Sum Of new_patients],
Sum(established_patients) AS [Sum Of established_patients],
Sum(total_patients) AS [Sum Of total_patients],
Sum(angiograms) AS [Sum Of angiograms],
Sum(fundus) AS [Sum Of fundus],
Sum(ascan) AS [Sum Of ascan],
Sum(bscan) AS [Sum Of bscan],
Sum(cataracts) AS [Sum Of cataracts],
Sum(retinal) AS [Sum Of retinal],
First(log_date) AS [First Of log_date],
First(service_date) AS [First Of service_date]
FROM Patients
WHERE service_date >
DateSerial(Year(Date()), Month(Date()), 1)
AND service_date <
DateSerial(Year(Date()), Month(Date()) + 1, 1);

SELECT Sum(Patients.new_patients) AS [Sum Of
new_patients], Sum(Patients.established_patients) AS [Sum
Of established_patients], Sum(Patients.total_patients) AS
[Sum Of total_patients], Sum(Patients.angiograms) AS [Sum
Of angiograms], Sum(Patients.fundus) AS [Sum Of fundus],
Sum(Patients.ascan) AS [Sum Of ascan], Sum(Patients.bscan)
AS [Sum Of bscan], Sum(Patients.cataracts) AS [Sum Of
cataracts], Sum(Patients.retinal) AS [Sum Of retinal],
First(Patients.log_date) AS [First Of log_date], First
(Patients.service_date) AS [First Of service_date] FROM
Patients WHERE Patients.service_date > DateSerial(Year(Date
()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month
(Date()) + 1, 1);

This doesnt work for current month :(
-----Original Message-----
Would Month(Service_date)=Month(Now()) work for Current
Month
and Month(Service_date)=Month(Now()-1) work for previous
month?
Click to expand...

Not quite: it would get you the results for March 2004, March 2003,
March 2002, etc. - any data where *just the month* matches.

The most efficient way (which takes advantage of an Index on the
Service_Date field) would be:

Current month -

[ServiceDate] > DateSerial(Year(Date()), Month(Date()), 1) AND <
DateSerial(Year(Date()), Month(Date()) + 1, 1)

Previous month -

[ServiceDate] > DateSerial(Year(Date()), Month(Date()) - 1, 1) AND <
DateSerial(Year(Date()), Month(Date()), 1)




.
Click to expand...
Click to expand...
 
J

John Vinson

SELECT Sum(Patients.new_patients) AS [Sum Of
new_patients], Sum(Patients.established_patients) AS [Sum
Of established_patients], Sum(Patients.total_patients) AS
[Sum Of total_patients], Sum(Patients.angiograms) AS [Sum
Of angiograms], Sum(Patients.fundus) AS [Sum Of fundus],
Sum(Patients.ascan) AS [Sum Of ascan], Sum(Patients.bscan)
AS [Sum Of bscan], Sum(Patients.cataracts) AS [Sum Of
cataracts], Sum(Patients.retinal) AS [Sum Of retinal],
First(Patients.log_date) AS [First Of log_date], First
(Patients.service_date) AS [First Of service_date] FROM
Patients WHERE Patients.service_date > DateSerial(Year(Date
()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month
(Date()) + 1, 1);

This doesnt work for current month :(
Click to expand...

I think you need to add one thing to the WHERE clause:

WHERE Patients.service_date >= DateSerial(Year(Date()), Month(Date()),
1) AND < Patients.service_date < DateSerial(Year(Date()),
Month(Date()) + 1, 1);

That is, you want the ServiceDate to start - greater than OR EQUAL TO
- the first of the month, and less than the first of next month.

What exactly do you mean by "does not work"?
 
N

Nikos Yannacopoulos

Well, the answer is there in my previous reply; have a second, more careful
look!

Nikos
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Previous Month Query 5
Display last month and year 2
All data until start of current month 2
Current month in the criteria of a query 5
Help required on Tabulating monthly expenses for the Current Month,Previous Month and Month previous 12
Trend By Month 2
Previous Week, Month, year, etc. 2
Query for previous week, Sun thru Sat, from date field 1

Top