Query to check for non-existent Records

[Lance]

Hello,

I have two tables that I am trying to build this query on.

One table lists all Organizations that are participating in a program.
We'll call that tblProgram

One table lists all of the forms that organization must submit. We'll
call that tblForm

One table tracks all of the forms that each organization has submitted.
We'll call that tblReceived

The structure of the tables is fixed.

So, what I am trying to figure out is a way to take all organizations
that exist in tblProgram, and then take all of the forms in tblForm,
and have a way to return a list of all combinations of Organizations
and Forms that do NOT exist in tblReceived.

Thanks!

 

[Marshall Barton]

I have two tables that I am trying to build this query on.

One table lists all Organizations that are participating in a program.
We'll call that tblProgram

One table lists all of the forms that organization must submit. We'll
call that tblForm

One table tracks all of the forms that each organization has submitted.
We'll call that tblReceived

The structure of the tables is fixed.

So, what I am trying to figure out is a way to take all organizations
that exist in tblProgram, and then take all of the forms in tblForm,
and have a way to return a list of all combinations of Organizations
and Forms that do NOT exist in tblReceived.

That's three tables

SELECT O.org, F.form
FROM (Organizations As O LEFT JOIN tblForm As F
ON O.orgid = F.orgid) LEFT JOIN tblReceived As R
ON F.formid = F.formid
WHERE F.formid Is Null

 

[Lance]

Marsh,

Indeed, I suppose it IS three tables.

I am having some problems figuring out why I cannot get your query to
work.

my exact query text is...


SELECT tblPrograms.OrgID, tblForm.Name
FROM (tblInfo LEFT JOIN tblForm ON tblInfo.LookupID = tblForm.Name)
LEFT JOIN tblPrograms ON tblForm.OrgID = tblPrograms.OrgID

WHERE (((tblInfo.LookupID) Like "F" & "##" And
(tblInfo.LookupID)<>"F00"))

Where tblPrograms is equivalent to tblOrg I had presented earlier,
tblForm is actually the name of my "received" table, and tblInfo is my
form list. The where clause is just something that is filtering the
query for the appropriate type of form from my list of forms.

Have I got the order of joins wrong? When I do this, I get a list of
just what I don't need, organizations and their associated forms. When
I add the "is null" clause to the FormID field, I get zero results. Not
sure why that is.

I am going to attribute it to user error though any advice is
appreciated.

 

[Marshall Barton]

Indeed, I suppose it IS three tables.

I am having some problems figuring out why I cannot get your query to
work.

my exact query text is...


SELECT tblPrograms.OrgID, tblForm.Name
FROM (tblInfo LEFT JOIN tblForm ON tblInfo.LookupID = tblForm.Name)
LEFT JOIN tblPrograms ON tblForm.OrgID = tblPrograms.OrgID

WHERE (((tblInfo.LookupID) Like "F" & "##" And
(tblInfo.LookupID)<>"F00"))

Where tblPrograms is equivalent to tblOrg I had presented earlier,
tblForm is actually the name of my "received" table, and tblInfo is my
form list. The where clause is just something that is filtering the
query for the appropriate type of form from my list of forms.

Have I got the order of joins wrong? When I do this, I get a list of
just what I don't need, organizations and their associated forms. When
I add the "is null" clause to the FormID field, I get zero results. Not
sure why that is.

I am going to attribute it to user error though any advice is
appreciated.

I can't be sure what with all the name changes, but it looks
like the correct joins to me.

Without the tblForm.Name Is Null criteria, it should return
the name of every form in tblInfo, whether it's used or not.
The Is Null criteria should filter out the ones that have
been returned leaving only the ones that were not returned.
Double check that there actually are some that have not been
returned.

Note that using a field named name is a potential problem
waiting to happen. You should change that to something more
descriptive such as FormName.

 

[Lance]

Marsh,

Okay, perhaps what I should do is explain a bit more about the nature
of the problem.

I went ahead and cleaned up the query a bit, and have been
troubleshooting it for a little while, with no avail.

When I create the query piece for OrgID.tblPrograms, I get a list of
all the organizations participating in the current program, all is well

SELECT OrgID from tblPrograms

When I create the left join onto tblForm, I am creating a left join
into the table that already houses the 'unified' objects. I think that
might be where the problem is coming in. From your first example, it
looked like your sequence went "organizations, left join onto form
names, left join onto actual form received data". In my sequence, it is
going "organizations, left join into forms received, left join onto
form names". When I do that, All I am getting are the names of the
forms received, and if I put "is null", I get no results. After
realzing this, I thought it'd be good to flip the tables, however,
there is no way to left join my organizations onto the form data is
there? What would be a unifying field?

 

[Marshall Barton]

Okay, perhaps what I should do is explain a bit more about the nature
of the problem.

I went ahead and cleaned up the query a bit, and have been
troubleshooting it for a little while, with no avail.

When I create the query piece for OrgID.tblPrograms, I get a list of
all the organizations participating in the current program, all is well

SELECT OrgID from tblPrograms

When I create the left join onto tblForm, I am creating a left join
into the table that already houses the 'unified' objects. I think that
might be where the problem is coming in. From your first example, it
looked like your sequence went "organizations, left join onto form
names, left join onto actual form received data". In my sequence, it is
going "organizations, left join into forms received, left join onto
form names". When I do that, All I am getting are the names of the
forms received, and if I put "is null", I get no results. After
realzing this, I thought it'd be good to flip the tables, however,
there is no way to left join my organizations onto the form data is
there? What would be a unifying field?

I see, the received table is a many-many junction table
between Orgs and Forms. In this case I had the order of the
joins out of whack. Let's try this instead:

SELECT O.OrgName, F.FormName
FROM Organizations As O LEFT JOIN (tblForm As F
LEFT JOIN tblReceived As R
ON F.formid = R.formid)
ON O.OrgID= F.OrgID)
WHERE R.formid Is Null

I am totally confused as to which name is used for which
field, especially if you really have a field named Name
(Access may think you want the table's name property). I
hope you can sort out what fields I think I'm using and the
ones you are really using.

 

[Lance]

Marsh,

You aren't the only one who is totally confused, so don't worry about
that part I will probably spend some time to go back and change
"name" to "FName" or something simliar, I didn't even consider there
would potential conflict there.I will report back when I have some more
success / failure on the query.