If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ranking in Access Query by Dates
I have db that holds tables that record employee production numbers. It has
all the associates, what jobprocesses they did and what date they did them, along with standard productivity information (units processed, time on task,....). Then I have another table that holds productivity goals. We keep track of a learning curve for the first 2 weeks an associate is in a jobprocess. So i have my goals set up for each day (day 1 they should be here, day 2 they should be at here, day 3 they should be here....) What I need to do is rank the days in productivity by jobprocess for each associate so I can later join in the goals and see where the associate is performing. So my main problem is that I can't get the days in productivity to rank correctly. This is an example of what I have: ID JobProcess Date 1 A 4-1 1 B 4-1 1 A 4-3 2 B 4-1 2 A 4-2 This is what I need: ID JobProcess Date DayRank 1 A 4-1 1 1 B 4-1 1 1 A 4-4 2 2 B 4-1 1 2 A 4-2 1 I tried setting up a table that took the min date for each jobprocess by associate then using DateDif for all other days, but the problem would be, if you look at the example above, row 3 would be ranked as a 4 rather than 2. And when you join w/ my Goals tables, then that associate would be expected to be at Day 4 goal for that process when that was actually only their 2nd day in that process and they should be expected to be at Day 2 goal. I tried this code: SELECT [New Console Data].[Employee Id], [New Console Data].[Function Name], [New Console Data].Date, (SELECT Count(*) FROM [New Console Data] AS T WHERE T.[Function Name] = [New Console Data].[Function Name] AND T.Date = [New Console Data].Date) AS Rnk FROM [New Console Data] GROUP BY [New Console Data].[Employee Id], [New Console Data].[Function Name], [New Console Data].Date; But the problem is that it is ranking against each every record in my table (over 100K)--example, i only have 2 months of data in my table but its ranking one associate as being in the 2K range. Can anybody give some advice? |
#2
|
|||
|
|||
Ranking in Access Query by Dates
Have you tried:
SELECT [New Console Data].[Employee Id], [New Console Data].[Function Name], [New Console Data].Date, (SELECT Count(*) FROM [New Console Data] AS T WHERE T.[Function Name] = [New Console Data].[Function Name] AND T.[Employee ID] = [New Console Data].[Employee ID] AND T.Date = [New Console Data].Date ) AS Rnk FROM [New Console Data] GROUP BY [New Console Data].[Employee Id], [New Console Data].[Function Name], [New Console Data].Date; where the sub-query just added a condition on Employee ID, since you want rank over date, but BY function name and BY employee. Vanderghast, Access MVP "TinaP" wrote in message ... I have db that holds tables that record employee production numbers. It has all the associates, what jobprocesses they did and what date they did them, along with standard productivity information (units processed, time on task,....). Then I have another table that holds productivity goals. We keep track of a learning curve for the first 2 weeks an associate is in a jobprocess. So i have my goals set up for each day (day 1 they should be here, day 2 they should be at here, day 3 they should be here....) What I need to do is rank the days in productivity by jobprocess for each associate so I can later join in the goals and see where the associate is performing. So my main problem is that I can't get the days in productivity to rank correctly. This is an example of what I have: ID JobProcess Date 1 A 4-1 1 B 4-1 1 A 4-3 2 B 4-1 2 A 4-2 This is what I need: ID JobProcess Date DayRank 1 A 4-1 1 1 B 4-1 1 1 A 4-4 2 2 B 4-1 1 2 A 4-2 1 I tried setting up a table that took the min date for each jobprocess by associate then using DateDif for all other days, but the problem would be, if you look at the example above, row 3 would be ranked as a 4 rather than 2. And when you join w/ my Goals tables, then that associate would be expected to be at Day 4 goal for that process when that was actually only their 2nd day in that process and they should be expected to be at Day 2 goal. I tried this code: SELECT [New Console Data].[Employee Id], [New Console Data].[Function Name], [New Console Data].Date, (SELECT Count(*) FROM [New Console Data] AS T WHERE T.[Function Name] = [New Console Data].[Function Name] AND T.Date = [New Console Data].Date) AS Rnk FROM [New Console Data] GROUP BY [New Console Data].[Employee Id], [New Console Data].[Function Name], [New Console Data].Date; But the problem is that it is ranking against each every record in my table (over 100K)--example, i only have 2 months of data in my table but its ranking one associate as being in the 2K range. Can anybody give some advice? |
Thread Tools | |
Display Modes | |
|
|