Need assistance modifying date query

[tryingtolearn]

I have a query that is based off of a union query

SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs
FROM clvpertech
GROUP BY clvpertech.clvtech1;

This is returning a sum of all the procedures a technician does for the entire database.
This is working.

I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.

I made this query to get the daily results and it works

SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$()))
GROUP BY clvpertech2.clvtech1;

How can I add different queries to get the results by breaking down the date field for year, week, and month?

I tried this for the month but I get Invalid Procedure Call when I try to run it???

SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4])))
GROUP BY clvpertech2.clvtech1;

 

[Brianwarnock]

I 'm not 100% certain I understand what you are trying to do, your 2nd query will list info when one of the dates equals todays date, which is not a breakdown by day, which would be achieved by grouping by date.

I was going to suggest that you look up Month and Year functions.

How you handle a week is much more difficult I think, atleast I have no off the cuff suggestion.

Brian

 

[tryingtolearn]

The current day is what I meant by day

Basically there are pay incentives tied to the highest producing tecnician.
Incentives for
Highest today,
Highest for the week
Highest for the month
Highest for the year.

The total cannot be tallied until there is a date complete in one or more of the 4 datecomp fields.
That part is all working so now I just need to know how to break it down by the week and the month and the year
Hope that makes more sense.

 

[tryingtolearn]

Nevermind

I guess I was making this alot harder then it needed to be.

Ended up just making a form with a start date and stop date then put the date range as a where condition in the query.

That works alot smoother.

 

[Brianwarnock]

Thanks for coming back, not sure how your solution ties in with your original requirement but then I don't think we were on the same wavelength, it happens

Brian

 

[tryingtolearn]

Well on the form if I put the start date as today and the end date as today I get the technicians productivity for just today so I can determine the highest producer.

If I put the start date as say Moday of last week and the end date as Friday of last week I will get the weeks highest producer and so on...

This works better for me because now I have endless historical data without having to manipulate anything for week starts and stops or last year or anything - I just put when I want to start getting data and stop getting data and all the info is right there.

Im probably not explaining it right but this is a huge learning curve for me but I appreciate you taking the time - It was actually your post that got me going in this direction - when you said handling the week would be more difficult - it got me thinking - how am I going to handle the years and then last week of December to first week of January with a new year and so on - so this simple solution came from that. Go Figure! Thanks

 

[Brianwarnock]

Happy to have been the catalyst, but we were on different wavelengths , when you said a breakdown by Month for example I thought that you wanted the figures for Jan, Feb, March etc in the one report; ditto for the others, which was why the weeks were going to be difficult.

You got there which is the main thing.

Brian