First, you need something in CQIPROCEDURES to find out what the last x records are, such as a DateTime field.
Next, you also need a primary key. Then, you'll have a subquery, where the TOP clause is gonna go, such as this:
SELECT ...
WHERE CQIPROCHIS.SurgeonID = ?gnCurrentSurgeonID
AND CQIPROCEDURES.<i>PrimaryKeyField</i> IN
(SELECT TOP <i>x PrimaryKeyField</i> FROM CQIPROCEDURES
WHERE CQIPROCEDURES.SurgeonID = CQIPROCHIS.SurgeonID
ORDER BY <i>DateTimeField</i> DESC)
GROUP BY proccode,description,procedureid
ORDER BY 1
There might be other ways, but right now that's the only one I can think of without further research.
Hope this helps!
>I have a table that records history of procedures by surgeons. I can get the history time history for all records for a certain surgeon, but I NEED to get just history for the last 5 or 10 or whatever number the user wants. We don't want this history go back forever.
Sylvain Demers