Analysis Questions

[Guest]

I have a table called Reservations_New that contains an Evaluator field,
Review Date and many other pieces of data. Another table contains the Skill
level of the person. If the person is an Evaluator, the skill level is 'QA'.

The request is for a report on whatever date range entered for the Review
Date that shows a random of 10 reservations audited (their name in the
Evaluator field) for all evaluators. This seems like it should be easy but
Murphy keeps raising his head.

I would like to use the one table that has the skill level = to 'QA' to look
for the evaluators name on the reservation table. But how do I limit to just
10 reservations for each Evaluator. If I look for just one person's name,
bypassing the skill level check, it is simple but trying to get 10 for
evaluator at once is driving me up a wall.

Any Ideas on how to proceed?

 

[Guest]

Table Skill_Level has fields EmpID & Skill. Use your field and table names.

Try this and add your other fields as needed --
SELECT Q.Evaluator, Q.[Review Date], (SELECT COUNT(*) FROM Reservations_New Q1
WHERE Q1.[Evaluator] = Q.[Evaluator]
AND Q1.[Review Date] < Q.[Review Date])+1 AS Rank
FROM Reservations_New AS Q INNER JOIN Skill_Level ON Q.Evaluator =
Skill_Level.EmpID
WHERE (((Q.[Review Date]) Between [Enter start] And [Enter end]) AND
(((SELECT COUNT(*) FROM Reservations_New Q1
WHERE Q1.[Evaluator] = Q.[Evaluator]
AND Q1.[Review Date] < Q.[Review Date])+1)<=10) AND
((Skill_Level.Skill)="QA"))
ORDER BY Q.Evaluator, Q.[Review Date];

Table Skill_Level has fields EmpID & Skill. Use your field and table names.

 

[Guest]

Karl,

Thanks a bunch. I just asked for a direction and you gave code!!! Awesome!
All of the people assisting have given more than asked!

Thanks again!!!

Table Skill_Level has fields EmpID & Skill. Use your field and table names.

Try this and add your other fields as needed --
SELECT Q.Evaluator, Q.[Review Date], (SELECT COUNT(*) FROM Reservations_New Q1
WHERE Q1.[Evaluator] = Q.[Evaluator]
AND Q1.[Review Date] < Q.[Review Date])+1 AS Rank
FROM Reservations_New AS Q INNER JOIN Skill_Level ON Q.Evaluator =
Skill_Level.EmpID
WHERE (((Q.[Review Date]) Between [Enter start] And [Enter end]) AND
(((SELECT COUNT(*) FROM Reservations_New Q1
WHERE Q1.[Evaluator] = Q.[Evaluator]
AND Q1.[Review Date] < Q.[Review Date])+1)<=10) AND
((Skill_Level.Skill)="QA"))
ORDER BY Q.Evaluator, Q.[Review Date];

Table Skill_Level has fields EmpID & Skill. Use your field and table names.
--
KARL DEWEY
Build a little - Test a little

I have a table called Reservations_New that contains an Evaluator field,
Review Date and many other pieces of data. Another table contains the Skill
level of the person. If the person is an Evaluator, the skill level is 'QA'.

The request is for a report on whatever date range entered for the Review
Date that shows a random of 10 reservations audited (their name in the
Evaluator field) for all evaluators. This seems like it should be easy but
Murphy keeps raising his head.

I would like to use the one table that has the skill level = to 'QA' to look
for the evaluators name on the reservation table. But how do I limit to just
10 reservations for each Evaluator. If I look for just one person's name,
bypassing the skill level check, it is simple but trying to get 10 for
evaluator at once is driving me up a wall.

Any Ideas on how to proceed?