Querying last months data

[Litedread]

Hi All,
I have created a query where I can get a count of a specific person's
activity. These people log when they recieve mail and when they have actioned
it. The problem that I have is these stats are gathered for the previous
month i.e. in January we are looking at Decembers data. I can get the query
to get the data from February to December but come January I cant go back to
December.
Here is the SQL code:
SELECT Count([Experience Rating Table].DateCompleted) AS CountOfDateCompleted
FROM [MyTable]
GROUP BY [MyTable].AssignedTo, Month([DateCompleted]), Year([DateCompleted])
HAVING ((([MyTable].AssignedTo)="PersonsName") AND
((Month([DateCompleted]))=Month(Now())-1) AND
((Year([DateCompleted]))=Year(Now())))

Any help would be greatly appriciated.

 

[John W. Vinson]

Hi All,
I have created a query where I can get a count of a specific person's
activity. These people log when they recieve mail and when they have actioned
it. The problem that I have is these stats are gathered for the previous
month i.e. in January we are looking at Decembers data. I can get the query
to get the data from February to December but come January I cant go back to
December.

You can use the builtin DateSerial() function to do this. I'm also moving your
code from the HAVING clause (which is run after all the calculations and
totals are done, discarding most of them) to the WHERE clause (more efficient
because it's done first):

SELECT Count([Experience Rating Table].DateCompleted) AS CountOfDateCompleted
FROM [MyTable]
GROUP BY [MyTable].AssignedTo
WHERE [MyTable].AssignedTo="PersonsName" AND [DateCompleted] >=
DateSerial(Year(Date()), Month(Date()) - 1, 1) AND [DateCompleted] <
DateSerial(Year(Date()), Month(Date()), 1);

DateSerial is smart enough to perceive month 0 of 2010 as December of 2009.

 

[KARL DEWEY]

Try this --
HAVING (([MyTable].AssignedTo)="PersonsName") AND
([DateCompleted] Between DateAdd("m",
-1,DateSerial(Year(Date()),Month(Date()),1) AND
DateSerial(Year(Date()),Month(Date()),1)-1

 

[Marshall Barton]

Hi All,
I have created a query where I can get a count of a specific person's
activity. These people log when they recieve mail and when they have actioned
it. The problem that I have is these stats are gathered for the previous
month i.e. in January we are looking at Decembers data. I can get the query
to get the data from February to December but come January I cant go back to
December.
Here is the SQL code:
SELECT Count([Experience Rating Table].DateCompleted) AS CountOfDateCompleted
FROM [MyTable]
GROUP BY [MyTable].AssignedTo, Month([DateCompleted]), Year([DateCompleted])
HAVING ((([MyTable].AssignedTo)="PersonsName") AND
((Month([DateCompleted]))=Month(Now())-1) AND
((Year([DateCompleted]))=Year(Now())))

That Having clause needs to be a Where clause:

WHERE (AssignedTo = "PersonsName")
AND (DateCompleted =Between DateSerial(Year(Date(),
Month(Date())-1. 1) AND DateSerial(Year(Date(),
Month(Date()), 0))

 

[Litedread]

Thanks to all,
Got it working. Much appriciated!!!

Hi All,
I have created a query where I can get a count of a specific person's
activity. These people log when they recieve mail and when they have actioned
it. The problem that I have is these stats are gathered for the previous
month i.e. in January we are looking at Decembers data. I can get the query
to get the data from February to December but come January I cant go back to
December.
Here is the SQL code:
SELECT Count([Experience Rating Table].DateCompleted) AS CountOfDateCompleted
FROM [MyTable]
GROUP BY [MyTable].AssignedTo, Month([DateCompleted]), Year([DateCompleted])
HAVING ((([MyTable].AssignedTo)="PersonsName") AND
((Month([DateCompleted]))=Month(Now())-1) AND
((Year([DateCompleted]))=Year(Now())))

That Having clause needs to be a Where clause:

WHERE (AssignedTo = "PersonsName")
AND (DateCompleted =Between DateSerial(Year(Date(),
Month(Date())-1. 1) AND DateSerial(Year(Date(),
Month(Date()), 0))