CODEWITHSUNDEEP
ms-access
user2119980
user2119980

Reputation: 509

Subqueries In Access

I am not very skilled when it comes to Access coding, I much rather prefer to work with SQL. I have a code set up to pull some employees in specific areas. But one table has an employees entire history, so I pulled in the field REC EFF STT DT which is when each position they may have had began. I only need the most recent one (their current) job. I know I need a Subquery in the WHERE statement including the MAX DATE but I cannot figure out how to format it properly.

SELECT O896IA_VEMPPRSA.EMP_NA, O896IA_VEMPPRSA.SYS_EMP_ID_NR, 
     ([O896IA_VEMPPRSA].[REG_NR]) & ([O896IA_VEMPPRSA].[DIS_NR]) AS RRDD, 
     O867IA_VJOBHST.BUS_UNT_REF_NR, O867IA_VJOBHST.REC_EFF_STT_DT
FROM O867IA_VJOBHST 
INNER JOIN O896IA_VEMPPRSA 
   ON O867IA_VJOBHST.SYS_EMP_ID_NR = O896IA_VEMPPRSA.SYS_EMP_ID_NR 
WHERE (((([O896IA_VEMPPRSA].[REG_NR]) & ([O896IA_VEMPPRSA].[DIS_NR])) 
       IN ("2010","2108","2029","2230","2580")) AND 
      ((O867IA_VJOBHST.BUS_UNT_REF_NR) IN     
      ("ISBOP","ISCIM","ISGAP","ISCRA","ISCTP","ISOPS","ISSCS","ISSSP","ISTTG")))
GROUP BY O896IA_VEMPPRSA.EMP_NA, O896IA_VEMPPRSA.SYS_EMP_ID_NR, 
      ([O896IA_VEMPPRSA].[REG_NR]) & ([O896IA_VEMPPRSA].[DIS_NR]), 
       O867IA_VJOBHST.BUS_UNT_REF_NR, O867IA_VJOBHST.REC_EFF_STT_DT;

I cannot simply use MAX DATE, because it will include the record for each job they have had. I just need their current one so their most recent Date.

Upvotes: 1

Views: 107

Answers (1)

Popo
Popo

Reputation: 2460

You should be able to add a condition in your where clause:

and O867IA_VJOBHST.REC_EFF_STT_DT = (select max([hist].REC_EFF_STT_DT) 
from O867IA_VJOBHST as [hist] 
where [hist].SYS_EMP_ID_NR = O896IA_VEMPPRSA.SYS_EMP_ID_NR)

this assumes there will not be two entries with the same max date.

Upvotes: 1

Related Questions