I want to do a MINUS operation equivalent. How?

[KjWhal]

this is vexing me.

I have 4 tables, 2 entities, 1 relationship and 1 lookup

Entities are tblOrg and tblForm
Relationship is tblVentureParticipation
Lookup is tblInfo (Has many different lookups)

When an organization (these are listed in tblOrg) turns in a form, a new record is inserted into tblForm with the ID of that organization and the ID of the form

I want to identify the forms that have NOT been turned in.

So I figured

Step 1:
Identify all organizations who are participating (WHERE tblOrg.OrgID = tblVentureParticipation.OrgID)

Step 2:
Join all of the previously selected organizations with all of the forms (FROM tblOrg, tblInfo WHERE tblInfo.LookupID LIKE 'F##')

Step 3:
Remove all of the entries in tblForm that match the result of Step 2.

I have verified that both of these individual queries return the right information

SELECT tblInfo.LookupValue, tblOrg.OrgName FROM tblOrg, tblVentureParticipation, tblInfo, tblForm WHERE ((tblOrg.OrgID = tblVentureParticipation.OrgID AND tblVentureParticipation.ProID = 'P01' AND tblVentureParticipation.Semester = 'S025') AND (tblInfo.LookupID LIKE 'F##' AND tblInfo.LookupID <> 'F00'))

SELECT tblInfo.LookupValue,tblOrg.OrgName
FROM tblOrg, tblForm, tblInfo
WHERE (tblOrg.OrgID = tblForm.OrgID) AND (tblForm.Name = tblInfo.LookupID AND tblInfo.LookupID LIKE 'F##' AND tblInfo.LookupID <> 'F00') );

However, because I am working in Access and not Oracle I cannot just stick a MINUS in between them. I tried doing a AND NOT EXISTS IN the second query but that returns nothing.

Hopefully that explains it well enough, I make no apologies for the structure of the tables, I didn't do it.

 

[neileg]

It's in the joins (which you haven't specified).

If you join the dataset that represents all participating organisations with all the returned forms with a Left join, you will find that where the form is not returned, you have a null from the forms table. So if you build this query and use Is Null as a criterion, I think this will give you what you want.