Current Month and Previous Month

  • Thread starter Thread starter John Doe
  • Start date Start date
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.
 
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
 
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.
 
Would Month(Service_date)=Month(Now()) work for Current
Month
and Month(Service_date)=Month(Now()-1) work for previous
month?

Please help
 
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)
 
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...
 
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...
 
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"?
 
Well, the answer is there in my previous reply; have a second, more careful
look!

Nikos
 
Back
Top