Tweak to capture Employee Count as of---

[HRISmess]

I have the following query that counts all employees in a specific status, before a specific date. I need to modify with appropriate syntax in Access...what tweaks am I required to make here? Or, is there a more simplistic way to capture the data?

Any help is appreciated! Cheers!

Query:

select Count(*)
from e.history_job j
where j.empl_rcd_nbr = 0
and j.empl_status in ('A','L')
and j.effdt = (select max(j1.effdt)
from e.history_job j1
where j1.emplid = j.emplid
and j1.empl_rcd_nbr = j.empl_rcd_nbr
and j1.effdt <= '31-DEC-06')
and j.effseq = (select max(j2.effseq)
from e.history_job j2
where j2.emplid = j.emplid
and j2.empl_rcd_nbr = j.empl_rcd_nbr
and j2.effdt = j.effdt)

/

 

[SkipVought]

Hi,

Without next to nothing about your tables, take a look at this criteria...

and j1.effdt       <= '31-DEC-06')

Skip,

_[red][/red]

 

[HRISmess]

Think I figured it out -- d@mn syntax. A lack of parentheses were the main culprits; replacing '' with ""; and reformatting date was also necessary.

Thanks!

QUERY:

select Count(*) As EECOUNT
from e_history_job j
where ((((j.empl_rcd_nbr) = 0)
and (j.empl_status) in ("A","L")
and (j.effdt) = (select max(j1.effdt)
from e_history_job j1
where j1.emplid = j.emplid
and j1.empl_rcd_nbr = j.empl_rcd_nbr
and j1.effdt <= #12/31/2006#)
and( j.effseq) = (select max(j2.effseq)
from ep_history_job j2
where j2.emplid = j.emplid
and j2.empl_rcd_nbr = j.empl_rcd_nbr
and j2.effdt = j.effdt)));