Query

[par428]

I have a master list of surgical procedures and comparing it to the past 2
years of procedures from that list. I want Access to tell me which procedures
from the master list were NOT done in that 2 year span.

 

[MGFoster]

I have a master list of surgical procedures and comparing it to the past 2
years of procedures from that list. I want Access to tell me which procedures
from the master list were NOT done in that 2 year span.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It helps if we know the structure of your table(s). So this is a guess.

One, probably faster, way:

SELECT P.procedure_id
FROM Procedures AS P LEFT JOIN ProceduresPerformed AS PP
ON P.procedure_id = PP.procedure_id
WHERE PP.procedure_id IS NULL

Another way:

SELECT procedure_id
FROM Procedures
WHERE procedure_id NOT IN (SELECT procedure_id
FROM ProceduresPerformed)

Make sure procedure_id is indexed in both tables.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeTho4echKqOuFEgEQIdMgCgnIJcHHQCKt69b4AwXdzeVITEsyQAn0fj
liyJPLoPOqUMAN7Ujhhxil9c
=JvlI
-----END PGP SIGNATURE-----

 

[Marshall Barton]

I have a master list of surgical procedures and comparing it to the past 2
years of procedures from that list. I want Access to tell me which procedures
from the master list were NOT done in that 2 year span.

Try creating a new query using the Find Unmatched query
wizard.

 

[par428]

Thanks for your help Marshall,
I attempted to do what you said...I clicked on "new" and selected "Find
Unmatched Query Wizard"....my two tables were listed.
Table:Current Procedure Listing
Table: Procedure Rank Last 2 Years
I selected them both....I then matched two common fields called "Mnemonic"
and "Description".....I then placed both fields on my query result and viewed
the query....this gave me a large listing of procedures.......but when I
randomly selected procedures to validate whether they were booked in the past
2 years......I found a few. Which is not what I wanted.
Any other suggestions?

 

[par428]

Thanks for the reply Marshall.....I tried using

 

[Marshall Barton]

I attempted to do what you said...I clicked on "new" and selected "Find
Unmatched Query Wizard"....my two tables were listed.
Table:Current Procedure Listing
Table: Procedure Rank Last 2 Years
I selected them both....I then matched two common fields called "Mnemonic"
and "Description".....I then placed both fields on my query result and viewed
the query....this gave me a large listing of procedures.......but when I
randomly selected procedures to validate whether they were booked in the past
2 years......I found a few. Which is not what I wanted.

Post a Copy/Paste of your query's SQL view and we'll try to
straighten it out.