Top 2

[melissasiobhan]

I have a database of empoyees and trainings. Each employee muct be retrained every few years and it must be documented for the State.

I need to query and get the top 2 dates for each employee and training category. Nothing I have tried works. Top 2 only finds the last 2 dates and who took those classes. I know I am not grouping right.

I have a query of each training which in SQL looks like

SELECT tbl_TRAINREG.EMPLKEY, tbl_TRAINREG.EMPLTRAINKEY, tbl_TRAININGS.TRAINDATE, tbl_TRAININGS.TRAINCATKEY, tbl_TRAINREG.TRAINSTATUS

FROM tbl_TRAININGS INNER JOIN tbl_TRAINREG ON tbl_TRAININGS.TRAINKEY = tbl_TRAINREG.TRAINKEY

GROUP BY tbl_TRAINREG.EMPLKEY, tbl_TRAINREG.EMPLTRAINKEY, tbl_TRAININGS.TRAINDATE, tbl_TRAININGS.TRAINCATKEY, tbl_TRAINREG.TRAINSTATUS

HAVING (((tbl_TRAINREG.TRAINSTATUS)="COMPLETED"));

How can I do this in query to only show 2 dates per employee (EMPLKEY) & category (TRAINCATKEY)

I haven't been able to this in query - how about reports? If there is a way to limit the results in my report to only show 2 records per group I could do it that way.

Right now to get the report delivered I made a subreport of the dates for each category and made the detail section only large enough to show 2 rows and changed Can Grow to No. Not the best permenent solution - I know!

 

[southbean]

Try something like this:

SELECT TOP 2 Table.Field1,Table.Field2, Table.Field3,Table.Field4
FROM Helpdesk_Dat

You will have to use the SQL View to do this.

- Tom

 

[PHV]

You may try something like this:
SELECT B.EMPLKEY, B.EMPLTRAINKEY, A.TRAINDATE, A.TRAINCATKEY, B.TRAINSTATUS
FROM (tbl_TRAININGS A INNER JOIN tbl_TRAINREG B ON A.TRAINKEY = B.TRAINKEY)
INNER JOIN (
SELECT TOP 2 C.TRAINDATE, D.EMPLKEY, C.TRAINCATKEY
FROM tbl_TRAININGS C INNER JOIN tbl_TRAINREG D ON C.TRAINKEY = D.TRAINKEY
WHERE D.TRAINSTATUS='COMPLETED' AND D.EMPLKEY = B.EMPLKEY AND C.TRAINCATKEY = A.TRAINCATKEY
ORDER BY 1 DESC
) T ON B.EMPLKEY = T.EMPLKEY AND A.TRAINCATKEY = T.TRAINCATKEY AND A.TRAINDATE = T.TRAINDATE
WHERE B.TRAINSTATUS='COMPLETED'

Or something like this:
SELECT B.EMPLKEY, B.EMPLTRAINKEY, A.TRAINDATE, A.TRAINCATKEY, B.TRAINSTATUS
FROM tbl_TRAININGS A INNER JOIN tbl_TRAINREG B ON A.TRAINKEY = B.TRAINKEY
WHERE B.TRAINSTATUS='COMPLETED' AND A.TRAINDATE IN (
SELECT TOP 2 C.TRAINDATE FROM tbl_TRAININGS C INNER JOIN tbl_TRAINREG D ON C.TRAINKEY = D.TRAINKEY
WHERE D.TRAINSTATUS='COMPLETED' AND D.EMPLKEY = B.EMPLKEY AND C.TRAINCATKEY = A.TRAINCATKEY
ORDER BY 1 DESC
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886