Re: Need query to return absence of record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Jul 25, 10:21 am, Marshall Barton <marshbar...@xxxxxxxxxx> wrote:
sword...@xxxxxxxxx wrote:
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

--
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!

.

Relevant Pages

  • Re: Damsel in distress!
    ... I am trying to write this in SQL and your post ... >CREATE TABLE TimeCards ... > FROM Personnel AS P1, ... > Calendar AS C1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Alternative for TOP
    ... SELECT TOPis a proprietary feature that was easy to implement in SQL ... The idea is take each salary and build a group of other salaries ... by changing @n and playing with the two comparison operators. ... FROM Personnel AS P1, Personnel AS P2 ...
    (microsoft.public.sqlserver.programming)
  • Re: Can some explain this to me?
    ... Google for a recent posting of mine on scoping rules in SQL. ... learn how to properly name data elements -- why is a personnel ... DELETE FROM Personnel -- collective name ... Do entire companies expire in your data model? ...
    (comp.databases.ms-sqlserver)
  • Re: Need query to return absence of record
    ... time I do the query. ... FROM Personnel As P LEFT JOIN TrainingHistory As T ... I put in the SQL (except I changed T,ClassName is Null to T.Classname ...
    (microsoft.public.access.queries)
  • Re: Inner join on table with NULLs
    ... The problem is that the result of a SELECT query based on ... that the VBA function gets called for each and every record ... frontend database. ... Table Conf_Personnel contains a set of personnel records. ...
    (microsoft.public.access.queries)