Rolling 12 months data into crosstab problems

[kentwoodjean]

OK, I give up and need a bit of help or I will never get this done. Have a report that I need to calculate 12 months rolling data. While trying to use the same format I used for a different query, it is not working. So, have 2 problems: (1) 12 month rolling formula is not working and is only picking up the current month and (2) how do I get the reasons that are not in use by a person to stop showing in the crosstab and only show those that are being using by the individual [DESC]. Maybe if I get the rolling 12 months working, problem 2 will not be there.

Here is my SQL on my query and what I have thus Far. This is the query that will build my crosstab. My other working query for rolling 12 months has the DateSerial formula following "Date" (get just April 2005 using that) as well as criteria under the Date Field column (get nothing using this).

SELECT [WMI LNN EMP DETAIL 92660].EMPID, [WMI LNN EMP DETAIL 92660].OPID, [WMI LNN EMP DETAIL 92660].DESC, [WMI LNN EMP DETAIL 92660].DATE, [WMI LNN EMP DETAIL 92660].Lost, [WMI LNN EMP DETAIL 92660].EMPLSTNAME, DateSerial((Year(Now())-1),IIf(Month(Now())=1,12,Month(Now())-1),1) AS [Date]
FROM [WMI LNN EMP DETAIL 92660]
WHERE ((([WMI LNN EMP DETAIL 92660].SECTID)="62"))
GROUP BY [WMI LNN EMP DETAIL 92660].EMPID, [WMI LNN EMP DETAIL 92660].OPID, [WMI LNN EMP DETAIL 92660].DESC, [WMI LNN EMP DETAIL 92660].DATE, [WMI LNN EMP DETAIL 92660].Lost, [WMI LNN EMP DETAIL 92660].EMPLSTNAME, DateSerial((Year(Now())-1),IIf(Month(Now())=1,12,Month(Now())-1),1)
HAVING ((([WMI LNN EMP DETAIL 92660].DATE) Between (DateSerial((Year(Now())-1),Month(Now()),1)) And (DateSerial((Year(Now())),IIf(Month(Now())=1,12,Month(Now())-0),1))));

 

[lespaul]

could you also provide some sample data from the table and the expected results from the sample set?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[MichaelRed]

A "Trick" to making this work is to "Assign" a (month) number to the data set (oldest being the lowest - and presumably the lowest) number. I usually do this in a seperate operation prior to the XTab, then join the this with the XTab on the month by the date category, but use the assigned month number as the Piviot term. Then, sigh, another step (usually in the report) figure out the correct month name for the column caption(s).



MichaelRed

 

[kentwoodjean]

Lespaul, here is the sample data by month for employees who may or maynot have the same entries.

EMPID EMPLSTNAME EMPFSTNAME COSTCTR SECTID
10602 Doe Jane 92660 61
10602 Doe Jane 92660 61
10702 Doe Jane 92660 61
10262 Jones Jean 92660 61
10262 Jones Jean 92660 61
10262 Jones Jean 92660 61

OPID DESC DATE Lost Non-Prod Production
007 Meetgs 200504 7.75
101 Union 200504 2.0
107 Calls 200504 4.5
008 Train 200406
009 Sick 200406 7.75
007 FMLA 200406 15.50
109 Proj 200406 12.75


Results from the sample set would show only the lost time and specific reason/s for each person, not all of the reasons even though they did not use them.

Doe,Jane 200406 Meetings 7.75

Jones, Jean 200406 Sick 7.75


Hope this helps.