Query to return only last month data - Access 2000 1

[Shusha52]

Hi there,

I have tried all of the above help material and nothing seems to work. What i am looking for is this. I have stats that need to be extracted without me having to manually change the between periods.. for an activity date. The help I am looking for is that that of the 200,000 records or more only 4000 was from last month.. Is there any way the user can run my report that has a crosstab source where i specify one of the generic examples provided above to show them all stats for last month ONLY.
What i want is when the corsstab is run it shows all staff hrs for the last month only. Right now i have a simple query that pulls in the date based on the =(date()-31) but as we know there are some mths with less than 31.
If there is a simple one i can use to show me all of last mth only it wld be helpful

Any help is appreciated. thks in advance
thks

 

[traingamer]

Instead of calling it last month's data, call it last 30 days and remove all confusion...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Shusha52]

Thks for the fast response. What i am looking for is when the supervisors run their team stats, lets say they run it on the 1st of every mth, and today's date is 01-apr-2008 or 4-apr-2008, they want to see all stats for the mth of March ONLY. that meeans at any given point in time they want to be able to see the previous mths stats.
What func wld i be able to use for this other than the (Date()-31)
Usha

 

[PHV]

WHERE Format([your date field],'yyyymm')=Format(DateAdd('m',-1,Date()),'yyyymm')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Shusha52]

This will not work as it gives me an error. my date is in this 01-Mar-2008.
I Put the =(date()-31) in the criteria field. Pls let me know what can go into the criteria field comfortably ?
Usha

 

[PHV]

I don't play with the (buggy) query grid but he SQL view pane.
Please, post the WHERE clause of your actual SQL code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Shusha52]

Hi PHV,

Here is the sql view of that query:

SELECT PATACTIVITY.WorkLoadTypeDesc, PATACTIVITY.ResourceDesc, PATACTIVITY.WorkLoadTypeDesc, PATACTIVITY.tdyr, PATACTIVITY.tdy, PATACTIVITY.ActivityDateTime, PATACTIVITY.TEAMS
FROM PATACTIVITY
WHERE (((PATACTIVITY.WorkLoadTypeDesc) Not Like "Others") AND ((PATACTIVITY.ActivityDateTime)>=(Date()-31)))
ORDER BY PATACTIVITY.WorkLoadTypeDesc, PATACTIVITY.ActivityDateTime;


Hope this helps.thks
Usha

 

[PHV]

I'd use this:

SELECT WorkLoadTypeDesc, ResourceDesc, tdyr, tdy, ActivityDateTime, TEAMS
FROM PATACTIVITY
WHERE WorkLoadTypeDesc<>'Others'
AND Format([ActivityDateTime],'yyyymm')=Format(DateAdd('m',-1,Date()),'yyyymm')
ORDER BY WorkLoadTypeDesc, ActivityDateTime

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Shusha52]

Hi PH,
Thank you ever so much. It worked. Thks for the prompt response and glad there are such helpful people around. thks Again
Usha

 

[Shusha52]

Hi PH,

The query worked and showed me only the records that were supposed to be seen. this query is the forerunner for another crosstabe query. Is there some way ican allow the user to just enter 1 to 12 for the mths they want to see. in this case the first query for which you helped me can i allow the user to specify which mth they want without just the previous mth being shown. Pls help
Usha

 

[Shusha52]

Hi there,

I managed to get the lat month working with PHV's help. and many thanks for the same. Is there a function that can be used similar to what PHV gave that will show all activities for this mth.

the SQL i have now is as per shown to me ;
SELECT WorkLoadTypeDesc, ResourceDesc, tdyr, tdy, ActivityDateTime, TEAMS
FROM PATACTIVITY
WHERE WorkLoadTypeDesc<>'Others'
AND Format([ActivityDateTime],'yyyymm')=Format(DateAdd('m',-1,Date()),'yyyymm')
ORDER BY WorkLoadTypeDesc, ActivityDateTime

How wld i show all stats entered for this mth. I am not that great with sql and hence the problem.

is there a way to allow users to say which mth they want in response to a question like [Which mth do u wish to see stats for? ] and based on their response such as 3 for March or 4 April and so forth. The activitydate time is a date field. How can i allow users to select by any given mth.

Thanks so much for all your help.

Usha