Ranking in Access Query by Dates

[TinaP]

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?

 

[vanderghast]

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

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?