Query for absent data

[Richardson]

Hi,
I have a database that tracks programs in which individuals are
participating. I have a query/report that I am unable to create, and both
are necessary.
The first table is a list of all individuals titled "members".
The second table, " programs" is a list of all the programs they are
registed in, tied by a member id field to the first table, "members".

The output I need is a list of all the members not in a particular program.
For instance, I have 1500 total members and 35 are not in the Health
Education class. I need a report to show that list of 35 people. It would
also be nice to have it show me the classes they are in, but that is not
necessary.

I think it would be poor design, but the only idea I have had is to somehow
have the program search the programs table and then check of a field in the
members table indicating yes/no if they are registered.

Any help you can offer would be appreciated.
Thanks,
Lori

 

[Van T. Dinh]

Use a Query with an SQL String like:

SELECT M.MemberID
FROM tblMember AS M
LEFT JOIN tblProgram AS P
ON M.MemberID = P.MemberID
WHERE (P.Program = 'Health Education')
AND (P.MemberID Is Null)

The extra Field is not a good idea since it is a calculated Field.

 

[Richardson]

What you suggested is close to what I have, but apparently I am doing
something wrong. My statement looks like this:
SELECT Members.MemberID
FROM Members LEFT JOIN Policies ON Members.MemberID = Programs.MemberID
WHERE (((Programs.Program)="Health Education") and (Programs.MemberID Is
Null));

I am getting a blank recordset as the output.
Each entry in tblPrgrams has a memberID.

Table programs looks like this:
Registration#
MemberID
Program
other fields

Lori

 

[Van T. Dinh]

How come you used "Programs" in the criteria but you used "Policies" in the
Join?

 

[Richardson]

That was a mistype. It should be Programs in both locations.

 

[Van T. Dinh]

The Field Program in Table Programs is not a LookUp Field, is it?

Open the Table Programs in DesignView, find and post the Data Type of the
Field Program.

Hint: If it is a LookUp Field, you are not looking for "Health Education".
You are looking for a number which correspond to "Health Education".

 

[Richardson]

It isn't a lookup field. I can run the query without the Is Null statement
and get a list of people in the Health Education class, but there is no
entry with MemberID null, so I don't see how to get the list of members that
are not in this list.

Lori

 

[Richardson]

Got it! While I was waiting to get a response on my last post, I played
around with it. I took the SQL statement you gave me, minus the is null
statement, joined it with the members table in a left join query and put the
Is Null statement back into the MemberId field and I am getting the results
I need.
Thank you for the help. Now I just have one more really strange
query/report to work out.

Lori

 

[Van T. Dinh]

OK. The previous SQL String is not going to work, I think. Try the
Quantification Test using a SubQuery:

SELECT M.MemberID
FROM tblMember AS M
WHERE M.MemberID Not IN
(
SELECT DISTINCT P.MemberID
FROM Programs AS P
WHERE P.Program = 'Health Education'
)

I am fairly sure you can remove the keyword DISTINCT also.

There may be other more efficient SQL Strings as "Not IN" is not the most
efficient. At least, I didn't use correlated SubQuery for the
Quantification which is even worse.