Need to re-write and INTERSECT Query that will work in an older version of Access or

[Harry Schueler]

This is the query using 1 table.

select hrtemno, hrtdpcd, hrttodt, hrtgpno from hraact where hrttrtp IN ('p','l')
intersect select hrtemno, hrtdpcd, hrttodt, hrtgpno from hraact where hrttrtp = 'm'

[MMcCarthy]

Hi Harry, Welcome to Bytes.

Unfortunately Access doesn't support Intersect queries. A work around would be to use a combination of the inner join and a sub query as follows.

SELECT hrtemno, hrtdpcd, hrttodt, hrtgpno 
FROM hraact AS h1 INNER JOIN (SELECT hrtemno, hrtdpcd, hrttodt, hrtgpno FROM hraact WHERE hrttrtp = 'm') AS h2
ON h1.hrtemno=h2.hrtemno
where h1.hrttrtp IN ('p','l')

I made an assumption that hrtemno was your primary key.

[Harry Schueler]

Thanks, I was trying to write something like that, but was not even close!