Query Returns Nothing

[Tanya Lee]

HELP...this is slowly but surely driving me crazy!


The purpose of my query is to find out who has NOT had additional training.
(Additional training is its own table)

I have a query with 3 tables. All tables are joined by employee ID. Query
works fine. If I run query at this point, I see all employees who have taken
additional training and what type of training it was. (EE ID and first and
last name come from the Employee Information table, Business Unit comes from
the Training table, and additional training information comes from the
Additional Training table).

I only want to see employees in our Las Vegas office, so under Business
Unit, I typed "las vegas" for the criteria. Query works fine.

Next, I only want to see who in the Las Vegas office has NOT had additional
training, so I typed "null" in the criteria under Training Date. I have 2
fields from the Additional Training table: Training Date and Description. I
tried to each one, I tried to take out one of the fields, I tried typing
"null" in both. No matter what I try, I get 0 results. Nothing comes back
when I run the query. I'm obviously not doing something right...do I have the
wrong idea about how to get 'blanks' returned?

Does anyone know what I could be doing wrong???

Thank you.
Tanya

 

[Dale Fye]

Tanya,

Post your the query's SQL statement so we can actually see the tables and
fields you are using. I could propose a solution, but it would just be a
guess without viewing what you are already doing.

Dale

 

[Tanya Lee]

Ok here goes - this is the query BEFORE trying to see who has NOT had any
training:

SELECT [Additional Training].[Employee ID], [Employee Information].[Last
Name], [Employee Information].[First Name], [Training Information].[Hire
Date], [Training Information].[BU Name], [Additional Training].[Training
Date], [Additional Training].[Description of Training]
FROM ([Employee Information] INNER JOIN [Training Information] ON [Employee
Information].ID = [Training Information].ID) INNER JOIN [Additional Training]
ON [Employee Information].ID = [Additional Training].[Employee ID]
WHERE ((([Training Information].[BU Name])="las vegas"));

THIS IS WHAT THE QUERY LOOKS LIKE IF I ASK IT TO LIST EMPLOYEES WHO HAVE NOT
HAD ANY ADDITIONAL TRAINING:

SELECT [Additional Training].[Employee ID], [Employee Information].[Last
Name], [Employee Information].[First Name], [Training Information].[Hire
Date], [Training Information].[BU Name], [Additional Training].[Training
Date], [Additional Training].[Description of Training]
FROM ([Employee Information] INNER JOIN [Training Information] ON [Employee
Information].ID = [Training Information].ID) INNER JOIN [Additional Training]
ON [Employee Information].ID = [Additional Training].[Employee ID]
WHERE ((([Training Information].[BU Name])="las vegas") AND (([Additional
Training].[Training Date]) Is Null));

Obviously I'm not understanding how this query works.

Thank you!
Tanya

 

[John W. Vinson]

HELP...this is slowly but surely driving me crazy!


The purpose of my query is to find out who has NOT had additional training.
(Additional training is its own table)

I have a query with 3 tables. All tables are joined by employee ID. Query
works fine. If I run query at this point, I see all employees who have taken
additional training and what type of training it was. (EE ID and first and
last name come from the Employee Information table, Business Unit comes from
the Training table, and additional training information comes from the
Additional Training table).

I only want to see employees in our Las Vegas office, so under Business
Unit, I typed "las vegas" for the criteria. Query works fine.

Next, I only want to see who in the Las Vegas office has NOT had additional
training, so I typed "null" in the criteria under Training Date. I have 2
fields from the Additional Training table: Training Date and Description. I
tried to each one, I tried to take out one of the fields, I tried typing
"null" in both. No matter what I try, I get 0 results. Nothing comes back
when I run the query. I'm obviously not doing something right...do I have the
wrong idea about how to get 'blanks' returned?

Does anyone know what I could be doing wrong???

Probably using the default "Inner Join". Click the join line and
select option 2 (or 3) - "Show all records in [Employees] and matching
records in [Training]".

If this isn't clear or doesn't help, please open the query in SQL
view and post the SQL text here.

John W. Vinson [MVP]

 

[Tanya Lee]

THANK YOU THANK YOU!! That is something I didn't know. I tried it and it
worked. Thanks so very much!

Tanya

HELP...this is slowly but surely driving me crazy!


The purpose of my query is to find out who has NOT had additional training.
(Additional training is its own table)

I have a query with 3 tables. All tables are joined by employee ID. Query
works fine. If I run query at this point, I see all employees who have taken
additional training and what type of training it was. (EE ID and first and
last name come from the Employee Information table, Business Unit comes from
the Training table, and additional training information comes from the
Additional Training table).

I only want to see employees in our Las Vegas office, so under Business
Unit, I typed "las vegas" for the criteria. Query works fine.

Next, I only want to see who in the Las Vegas office has NOT had additional
training, so I typed "null" in the criteria under Training Date. I have 2
fields from the Additional Training table: Training Date and Description. I
tried to each one, I tried to take out one of the fields, I tried typing
"null" in both. No matter what I try, I get 0 results. Nothing comes back
when I run the query. I'm obviously not doing something right...do I have the
wrong idea about how to get 'blanks' returned?

Does anyone know what I could be doing wrong???

Probably using the default "Inner Join". Click the join line and
select option 2 (or 3) - "Show all records in [Employees] and matching
records in [Training]".

If this isn't clear or doesn't help, please open the query in SQL
view and post the SQL text here.

John W. Vinson [MVP]

 

[John W. Vinson]

SELECT [Additional Training].[Employee ID], [Employee Information].[Last
Name], [Employee Information].[First Name], [Training Information].[Hire
Date], [Training Information].[BU Name], [Additional Training].[Training
Date], [Additional Training].[Description of Training]
FROM ([Employee Information] INNER JOIN [Training Information] ON [Employee
Information].ID = [Training Information].ID) INNER JOIN [Additional Training]
ON [Employee Information].ID = [Additional Training].[Employee ID]
WHERE ((([Training Information].[BU Name])="las vegas") AND (([Additional
Training].[Training Date]) Is Null));

Obviously I'm not understanding how this query works.

It works by finding all records in [Employee Information] who *DO*
have records in [Training Information] in Las Vegas, but with a NULL
date of training; and then matches those records to all employees who
have records in [Additional Training] but a NULL value in the date.

The INNER JOIN returns only those records which *do* have matches, so
you will only see records for those employees whose Employee ID does
in fact exist in the Additional Training table; putting the IS NULL
criteria on the date probably excludes all of those, since there would
generally not be an existing record in the training table with a NULL
in the date field.

I suspect you need change only one word - INNER to LEFT - to get all
employees who *have* had training in Las Vegas, but who have *not* had
any Additional Training:

SELECT [Additional Training].[Employee ID], [Employee
Information].[Last Name], [Employee Information].[First Name],
[Training Information].[Hire Date], [Training Information].[BU Name],
[Additional Training].[Training Date],
[Additional Training].[Description of Training]
FROM ([Employee Information]
INNER JOIN [Training Information]
ON [Employee Information].ID = [Training Information].ID)
LEFT JOIN [Additional Training]
ON [Employee Information].ID = [Additional Training].[Employee ID]
WHERE ((([Training Information].[BU Name])="las vegas") AND
(([Additional Training].[Training Date]) Is Null));

The LEFT JOIN returns all records from the left table (here, from the
query joining [Employee information] to [Training Information],
whether or not they have a match in the right table (here, [Additional
Training]); if there is no match, you will get NULL values assigned to
all that table's fields.

John W. Vinson [MVP]