Need query to return absence of record

[sword856]

Hello all, hope everyone is doing well this fine morning!

I need some help in constructing a query. I have three tables,
designed as so:

Personnel
FullName(PK)
Department

Classes
ClassName(PK)

TrainingHistory *Junc table between Personnel and Classes*
FullName(FK)
ClassName(FK)
DateTaken

TrainingHistory records every instance of a class taken by all
personnel. I need to be able to find people who HAVE NOT taken a
given class. I need to be able to pick what class I search for each
time I do the query. For example, I need to know who in the
organization has NOT taken "Network Security" so that I can sign them
up. Any help on this would be much appreciated.

TIA,
George

 

[Marshall Barton]

Hello all, hope everyone is doing well this fine morning!

I need some help in constructing a query. I have three tables,
designed as so:

Personnel
FullName(PK)
Department

Classes
ClassName(PK)

TrainingHistory *Junc table between Personnel and Classes*
FullName(FK)
ClassName(FK)
DateTaken

TrainingHistory records every instance of a class taken by all
personnel. I need to be able to find people who HAVE NOT taken a
given class. I need to be able to pick what class I search for each
time I do the query. For example, I need to know who in the
organization has NOT taken "Network Security" so that I can sign them
up.

SELECT P.FullName
FROM Personnel As P LEFT JOIN TrainingHistory As T
ON P.FullName = T.FullName
WHERE T.ClassName = [Enter Class Name]
AND T,ClassName Is Null

 

[sword856]

Hello all, hope everyone is doing well this fine morning!

I need some help in constructing a query. I have three tables,
designed as so:


TrainingHistory *Junc table between Personnel and Classes*
FullName(FK)
ClassName(FK)
DateTaken

TrainingHistory records every instance of a class taken by all
personnel. I need to be able to find people who HAVE NOT taken a
given class. I need to be able to pick what class I search for each
time I do the query. For example, I need to know who in the
organization has NOT taken "Network Security" so that I can sign them
up.

SELECT P.FullName
FROM Personnel As P LEFT JOIN TrainingHistory As T
ON P.FullName = T.FullName
WHERE T.ClassName = [Enter Class Name]
AND T,ClassName Is Null

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks, Marsh, for replying!

I put in the SQL (except I changed T,ClassName is Null to T.Classname
is Null) just as you wrote, but the query is returning no names for
the classes I put in. I know not everyone has taken every class, so
there should be names showing up. The SQL makes it seem like the
query is looking for a value ([Enter Class Name]) and "Is Null" in the
same field, which seems like a contradiction. I may not have
explained well enough about my TrainingHistory table. Every time
someone takes a class, I enter their name, class name, and date in the
table. My explanation earlier made it seem like I put classes in that
everyone took. So, basically I am looking for the lack of records in
that table, i.e. records that would be [given class] and personnel
names. I hope that clears things up, and thanks for reading this!

 

[Jerry Whittle]

PARAMETERS [Enter ClassName] Text ( 255 );
SELECT Personnel.*, [Enter ClassName] AS [Missing Class]
FROM Personnel
WHERE Personnel.FullName Not In
(SELECT TrainingHistory.FullName
FROM TrainingHistory
WHERE TrainingHistory.ClassName In
(SELECT Classes.ClassName
FROM Classes
WHERE Classes.ClassName = [Enter ClassName]));

 

[John Spencer]

Simplest query with minimal records would be the following

SELECT Personnel.FullName
FROM Personnel
WHERE FullName Not IN
(SELECT FullName
FROM TrainingHistory
WHERE ClassName = [What Class])

If you have a large number of records, NOT IN can be slow. You MIGHT be
able to use the following or it might fail.

SELECT Personnel.FullName
FROM Personnel LEFT JOIN
(SELECT FullName
FROM TrainingHistory
WHERE ClassName = WhatClass) as TH
ON Personnel.FullName = TH.FullName

You can solve this with a two query approach.
Construct a query with everyone that HAS taken the class and save that
query.
Now use the unmatched query wizard to find everyone in the personnel table
that is not in the saved query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Hello all, hope everyone is doing well this fine morning!

I need some help in constructing a query. I have three tables,
designed as so:

Personnel
FullName(PK)
Department

Classes
ClassName(PK)

TrainingHistory *Junc table between Personnel and Classes*
FullName(FK)
ClassName(FK)
DateTaken

TrainingHistory records every instance of a class taken by all
personnel. I need to be able to find people who HAVE NOT taken a
given class. I need to be able to pick what class I search for each
time I do the query. For example, I need to know who in the
organization has NOT taken "Network Security" so that I can sign them
up. Any help on this would be much appreciated.

A NOT EXISTS query can do this for you:

SELECT FullName, Department FROM Personnel
WHERE Not Exists(SELECT FullName FROM TrainingHistory
WHERE TrainingHistory.FullName = Personnel.FullName
AND TrainingHistory.ClassName = [Enter class:]);

One big flaring warning here: FullName is NOT suitable as a primary key. A PK
should have three characteristics: it should be unique; it should be stable;
and it should (ideally) be short. People's names fail on all three counts! I
once worked with Dr. Lawrence David Wise, Ph.D. and his colleage, Dr. Lawrence
David Wise, Ph.D.; they're not stable, as people marry or otherwise change
their names; and they can be pretty long. A unique EmployeeID is a much better
choice as a PK.

John W. Vinson [MVP]

 

[sword856]

PARAMETERS [Enter ClassName] Text ( 255 );
SELECT Personnel.*, [Enter ClassName] AS [Missing Class]
FROM Personnel
WHERE Personnel.FullName Not In
(SELECT TrainingHistory.FullName
FROM TrainingHistory
WHERE TrainingHistory.ClassName In
(SELECT Classes.ClassName
FROM Classes
WHERE Classes.ClassName = [Enter ClassName]));
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello all, hope everyone is doing well this fine morning!

I need some help in constructing a query. I have three tables,
designed as so:


TrainingHistory *Junc table between Personnel and Classes*
FullName(FK)
ClassName(FK)
DateTaken

TrainingHistory records every instance of a class taken by all
personnel. I need to be able to find people who HAVE NOT taken a
given class. I need to be able to pick what class I search for each
time I do the query. For example, I need to know who in the
organization has NOT taken "Network Security" so that I can sign them
up. Any help on this would be much appreciated.

TIA,
George- Hide quoted text -

- Show quoted text -

Brilliant, Jerry! Works great! Thanks for your help!

 

[sword856]

PARAMETERS [Enter ClassName] Text ( 255 );
SELECT Personnel.*, [Enter ClassName] AS [Missing Class]
FROM Personnel
WHERE Personnel.FullName Not In
(SELECT TrainingHistory.FullName
FROM TrainingHistory
WHERE TrainingHistory.ClassName In
(SELECT Classes.ClassName
FROM Classes
WHERE Classes.ClassName = [Enter ClassName]));

- Show quoted text -

Brilliant, Jerry! Works great! Thanks for your help!- Hide quoted text -

- Show quoted text -

SELECT FullName, Department FROM Personnel
WHERE Not Exists(SELECT FullName FROM TrainingHistory
WHERE TrainingHistory.FullName = Personnel.FullName
AND TrainingHistory.ClassName = [Enter class:]);

One big flaring warning here: FullName is NOT suitable as a primary key. A PK
should have three characteristics: it should be unique; it should be stable;
and it should (ideally) be short. People's names fail on all three counts! I
once worked with Dr. Lawrence David Wise, Ph.D. and his colleage, Dr. Lawrence
David Wise, Ph.D.; they're not stable, as people marry or otherwise change
their names; and they can be pretty long. A unique EmployeeID is a much better
choice as a PK.

John W. Vinson [MVP]

Wow. That's a lot of ways to do this. John V., I tried yours cause
it seemed simplest and it works well.
The reason FullName is the PK is because when I started this DB, I
knew next to nothing about Access and was told by higher up to do so.
Now it is in it's later stages and has real data inside it. Would it
be possible to change the PK to an ID number and still keep all the
functionality it already has? Personnel has relationships on FullName
to 4 different tables, all of which have forms for input.

 

[Marshall Barton]

Hello all, hope everyone is doing well this fine morning!

I need some help in constructing a query. I have three tables,
designed as so:


TrainingHistory *Junc table between Personnel and Classes*
FullName(FK)
ClassName(FK)
DateTaken

TrainingHistory records every instance of a class taken by all
personnel. I need to be able to find people who HAVE NOT taken a
given class. I need to be able to pick what class I search for each
time I do the query. For example, I need to know who in the
organization has NOT taken "Network Security" so that I can sign them
up.

SELECT P.FullName
FROM Personnel As P LEFT JOIN TrainingHistory As T
ON P.FullName = T.FullName
WHERE T.ClassName = [Enter Class Name]
AND T,ClassName Is Null

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks, Marsh, for replying!

I put in the SQL (except I changed T,ClassName is Null to T.Classname
is Null) just as you wrote, but the query is returning no names for
the classes I put in. I know not everyone has taken every class, so
there should be names showing up. The SQL makes it seem like the
query is looking for a value ([Enter Class Name]) and "Is Null" in the
same field, which seems like a contradiction. I may not have
explained well enough about my TrainingHistory table. Every time
someone takes a class, I enter their name, class name, and date in the
table. My explanation earlier made it seem like I put classes in that
everyone took. So, basically I am looking for the lack of records in
that table, i.e. records that would be [given class] and personnel
names.

Sorry about all the typos. Try this

SELECT P.FullName
FROM Personnel As P LEFT JOIN TrainingHistory As T
ON P.FullName = T.FullName
WHERE T.ClassName = [Enter Class Name]
AND T.FullName Is Null

 

[John W. Vinson]

The reason FullName is the PK is because when I started this DB, I
knew next to nothing about Access and was told by higher up to do so.

Sometimes the higher-ups are idiots. Often they're quite bright... but still

Now it is in it's later stages and has real data inside it. Would it
be possible to change the PK to an ID number and still keep all the
functionality it already has?

Possible, but tedious and not tremendously easy.

Personnel has relationships on FullName
to 4 different tables, all of which have forms for input.

To do this, you'ld need to do several steps. Add the new (autonumber or
numeric or short unique text) Primary key to the Personnel table, and matching
foreign key fields (Long Integer if you use an autonumber) to the other four
tables. Populate the primary key field in the Personnel table, somehow - in
code, with an update query, depends on what you need.

Then run four Update queries; join Personnel to each of the other tables in
turn, joining on Fullname. Update the new foreign key field to

[Personnel].[newprimarykeyfieldname]

using your new primary key field name. Then break all the relationships on
Fullname, and establish new relationships on the new fields. You'll also
probably need to change the master/child link fields on all subforms which now
use Fullname.

John W. Vinson [MVP]

 

[sword856]

The reason FullName is the PK is because when I started this DB, I
knew next to nothing about Access and was told by higher up to do so.

Sometimes the higher-ups are idiots. Often they're quite bright... but still

Now it is in it's later stages and has real data inside it. Would it
be possible to change the PK to an ID number and still keep all the
functionality it already has?

Possible, but tedious and not tremendously easy.

Personnel has relationships on FullName
to 4 different tables, all of which have forms for input.

To do this, you'ld need to do several steps. Add the new (autonumber or
numeric or short unique text) Primary key to the Personnel table, and matching
foreign key fields (Long Integer if you use an autonumber) to the other four
tables. Populate the primary key field in the Personnel table, somehow - in
code, with an update query, depends on what you need.

Then run four Update queries; join Personnel to each of the other tables in
turn, joining on Fullname. Update the new foreign key field to

[Personnel].[newprimarykeyfieldname]

using your new primary key field name. Then break all the relationships on
Fullname, and establish new relationships on the new fields. You'll also
probably need to change the master/child link fields on all subforms which now
use Fullname.

John W. Vinson [MVP]

Ouch. That would mean I would also have to change forms and reports
because they would then show the employee ID numbers instead of
names. I trying to decide if it is worth it. Since the names wont be
in the tables on which the subforms are based, will I have to do
something complicated to display the names in the subform? or could I
maybe just keep the names in the tables and use them? Thanks so much
for your input, John.