Error message in query without a data match

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a trying to create an error free query that is
using 2 QUERIES (Students and 95a).

The Students Query is a list of all employees, and the 95a
Queries is a list of those employees that completed the
95a course.

Not all of the students are in the 95a query. When I
query these lists I get an error message for those
students not in the 95a list. I tried using the IsNull
statement, but the error message persists.

I want the result to be a zero for those employees who did
not complete the 95a course.

Can anyone help?
Thanks

SELECT Students_A.Empl, IIf(IsNull([95a]![Compl %]),0,
[95a]![Compl %]) AS [Comp % 95a], IIf(IsNull([95a]![Quiz
Avg %]),0,[95a]![Quiz Avg %]) AS [Quiz Avg % 95a]
FROM 95a RIGHT JOIN Students_A ON [95a].Empl =
Students_A.Empl;
 
Post the SQL of the 2 existing Queries (Students and 95a) and your attempted
combined Query.
 
Thanks for getting back. If I make a tanle each time
instead of basing these on a query all is fine.

Students Query:
SELECT DISTINCT [Progress 1 New].Empl
FROM [Progress 1 New];

95a Query:
SELECT [Progress 1 New].Empl, [Progress 1 New].Short,
[Progress 1 New].[Course Short], [Progress 1 New].[Compl
%], [Progress 1 New].[Quiz Avg %], [Progress 1 New].[Pre-
Assess Avg %]
FROM [Progress 1 New]
WHERE ((([Progress 1 New].Short)="95a"));

C95a Query (Joined w error):
SELECT Students.Empl, IIf(IsNull([95a]![Compl %]),0,[95a]!
[Compl %]) AS [Comp % 95a], IIf(IsNull([95a]![Quiz Avg
%]),0,[95a]![Quiz Avg %]) AS [Quiz Avg % 95a]
FROM Students LEFT JOIN 95a ON Students.Empl = 95a.Empl;

Here is the intial query Progress 1 New as well:
SELECT ImportProg.Empl, ImportProg.Sylabus, Sylabis.Short,
Sylabis.[Course Short], Val([CMP]) AS [Compl %], Val([QA])
AS [Quiz Avg %], Val([PA]) AS [Pre-Assess Avg %]
FROM ImportProg LEFT JOIN Sylabis ON ImportProg.Sylabus =
Sylabis.Sylab
WHERE (((ImportProg.Empl) Not In ('TEST-
NTS','1','TEST','STAMOS, JOHN','JIM GENNRICH','DELPHINO,
BRIAN','DEFAULT','135213','CHUCK','ZACK','DEWAYNE','Microso
ft Press Interactive
Training','Username:','Group:','NIKE','Progress
Summary','Totals:','Username','ADMIN','')))
ORDER BY ImportProg.Empl;

-----Original Message-----
Post the SQL of the 2 existing Queries (Students and 95a) and your attempted
combined Query.

--
HTH
Van T. Dinh
MVP (Access)




Dave said:
I have a trying to create an error free query that is
using 2 QUERIES (Students and 95a).

The Students Query is a list of all employees, and the 95a
Queries is a list of those employees that completed the
95a course.

Not all of the students are in the 95a query. When I
query these lists I get an error message for those
students not in the 95a list. I tried using the IsNull
statement, but the error message persists.

I want the result to be a zero for those employees who did
not complete the 95a course.

Can anyone help?
Thanks

SELECT Students_A.Empl, IIf(IsNull([95a]![Compl %]),0,
[95a]![Compl %]) AS [Comp % 95a], IIf(IsNull([95a]![Quiz
Avg %]),0,[95a]![Quiz Avg %]) AS [Quiz Avg % 95a]
FROM 95a RIGHT JOIN Students_A ON [95a].Empl =
Students_A.Empl;
Click to expand...


.
Click to expand...
 
Try using the dot operator rather than bang like:

SELECT Students.Empl,
IIf(IsNull([95a].[Compl %]),0,[95a].[Compl %]) AS [Comp % 95a],
IIf(IsNull([95a].[Quiz Avg %]),0,[95a].[Quiz Avg %]) AS [Quiz Avg % 95a]
FROM [Students] LEFT JOIN [95a]
ON [Students].Empl = [95a].Empl;

otherwise, the SQL looks correct.

--
HTH
Van T. Dinh
MVP (Access)


Dave said:
Thanks for getting back. If I make a tanle each time
instead of basing these on a query all is fine.

Students Query:
SELECT DISTINCT [Progress 1 New].Empl
FROM [Progress 1 New];

95a Query:
SELECT [Progress 1 New].Empl, [Progress 1 New].Short,
[Progress 1 New].[Course Short], [Progress 1 New].[Compl
%], [Progress 1 New].[Quiz Avg %], [Progress 1 New].[Pre-
Assess Avg %]
FROM [Progress 1 New]
WHERE ((([Progress 1 New].Short)="95a"));

C95a Query (Joined w error):
SELECT Students.Empl, IIf(IsNull([95a]![Compl %]),0,[95a]!
[Compl %]) AS [Comp % 95a], IIf(IsNull([95a]![Quiz Avg
%]),0,[95a]![Quiz Avg %]) AS [Quiz Avg % 95a]
FROM Students LEFT JOIN 95a ON Students.Empl = 95a.Empl;

Here is the intial query Progress 1 New as well:
SELECT ImportProg.Empl, ImportProg.Sylabus, Sylabis.Short,
Sylabis.[Course Short], Val([CMP]) AS [Compl %], Val([QA])
AS [Quiz Avg %], Val([PA]) AS [Pre-Assess Avg %]
FROM ImportProg LEFT JOIN Sylabis ON ImportProg.Sylabus =
Sylabis.Sylab
WHERE (((ImportProg.Empl) Not In ('TEST-
NTS','1','TEST','STAMOS, JOHN','JIM GENNRICH','DELPHINO,
BRIAN','DEFAULT','135213','CHUCK','ZACK','DEWAYNE','Microso
ft Press Interactive
Training','Username:','Group:','NIKE','Progress
Summary','Totals:','Username','ADMIN','')))
ORDER BY ImportProg.Empl;

-----Original Message-----
Post the SQL of the 2 existing Queries (Students and 95a) and your attempted
combined Query.

--
HTH
Van T. Dinh
MVP (Access)




Dave said:
I have a trying to create an error free query that is
using 2 QUERIES (Students and 95a).

The Students Query is a list of all employees, and the 95a
Queries is a list of those employees that completed the
95a course.

Not all of the students are in the 95a query. When I
query these lists I get an error message for those
students not in the 95a list. I tried using the IsNull
statement, but the error message persists.

I want the result to be a zero for those employees who did
not complete the 95a course.

Can anyone help?
Thanks

SELECT Students_A.Empl, IIf(IsNull([95a]![Compl %]),0,
[95a]![Compl %]) AS [Comp % 95a], IIf(IsNull([95a]![Quiz
Avg %]),0,[95a]![Quiz Avg %]) AS [Quiz Avg % 95a]
FROM 95a RIGHT JOIN Students_A ON [95a].Empl =
Students_A.Empl;
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top