Query help...PLEASE

[Guest]

I have a table with all my employees listed.

I have a table with certain tests that are given to them over the course of
a quarter. Fields in this table include "Test #" and "Test"

I have a form that sends the examiner's imput/results to a third table.

Problem: I am trying to create a query, then a report, that will allow me
to print all the employees names and show how many total tests they have
received. I am able to do this for employees who have received tests... but
I can not get the names of employees who have not received a tests to appear.
How do I get their names on the report indicating their count is "0"?

Thanks,

Doug

 

[Guest]

Hi,

You need a left outer join from your employees table to the other table.
When join create the join, place cursor on the line, and right click. You
will get join type selection. You want all records from employees table.

 

[Guest]

You need an outer join between your 2 tables. You get this by joining your 2
related fields and then double clicking on the join line and selecting the
type of join you want - in your case All the employees and ony those employee
tests that match
You then group the records and count the number of tests per employee -
those employees who have taken no tests will show up with a count of 0

The SQL will look something like:

SELECT zzEmp.Emp, Count(zzEmpTest.TestNo) AS CountOfTestNo
FROM zzEmp LEFT JOIN zzEmpTest ON zzEmp.Emp = zzEmpTest.Emp
GROUP BY zzEmp.Emp;

 

[Guest]

Great! Your advice created the query exactly like I needed, but..... When I
created the report it did not print the empty values. An further advice?

Thanks again,

Doug

 

[Guest]

OK, I figured it out, I got the empty fields to show up as "0"... But now....

I have "Date" as a field so I can bracket exactly the time periods of my
choosing. But when I add the criteria ">=[Enter begining date] And <=[Enter
ending date]" all the empty or null fileds disappear again....Grrrr.....

 

[John Vinson]

I have "Date" as a field so I can bracket exactly the time periods of my
choosing. But when I add the criteria ">=[Enter begining date] And <=[Enter
ending date]" all the empty or null fileds disappear again....Grrrr.....

Please post the SQL of the query.

Note that Date is a dangerous choice of fieldname: it's a reserved
word, for the builtin Date() function; Access may be assuming you want
to use today's date rather than the table field of that name.

John W. Vinson[MVP]

 

[Guest]

John,

I have since changed my query up a bit. I am using two tables "Tests" where
my test #s and names of the tests are stored.. and "Irv Tests" where all the
info goes from the form. I am trying to show the Test #, the Name of the
Test, and how many of each were performed. The problem below is still
occuring though...

I have joined the two tables and get the query to print exactly what I need.
But, when I add the "Date" field from the "Irv Tests" table with the
criteria... It stops printing the tests that were not performed.

Here is the SQL of the query:

SELECT Test.[Test #], Test.Test, Count([Irv Tests].Test) AS CountOfTest
FROM Test LEFT JOIN [Irv Tests] ON Test.[Test #] = [Irv Tests].[Test #]
WHERE ((([Irv Tests].Date)>=[Enter begining date] And ([Irv
Tests].Date)<=[Enter ending date]))
GROUP BY Test.[Test #], Test.Test
HAVING (((Test.[Test #]) Not Like "6*"))
ORDER BY Test.[Test #];

Thanks so much for taking the time,

I have "Date" as a field so I can bracket exactly the time periods of my
choosing. But when I add the criteria ">=[Enter begining date] And <=[Enter
ending date]" all the empty or null fileds disappear again....Grrrr.....

Please post the SQL of the query.

Note that Date is a dangerous choice of fieldname: it's a reserved
word, for the builtin Date() function; Access may be assuming you want
to use today's date rather than the table field of that name.

John W. Vinson[MVP]

 

[John Vinson]

I have joined the two tables and get the query to print exactly what I need.
But, when I add the "Date" field from the "Irv Tests" table with the
criteria... It stops printing the tests that were not performed.

Your setting a criterion on the [Irv Tests] date field is excluding
those records where the date field is NULL - which they will be if the
test was not performed. Try changing the HAVING criterion to a WHERE
criterion as well, to remove the 6* tests before doing the counting
(rather than counting them and then discarding the result:

SELECT Test.[Test #], Test.Test,
Count([Irv Tests].Test) AS CountOfTest
FROM Test LEFT JOIN [Irv Tests]
ON Test.[Test #] = [Irv Tests].[Test #]
WHERE (([Irv Tests].[Date]>=[Enter begining date]
And [Irv Tests].[Date]<=[Enter ending date])
OR [Irv Tests].[Date] IS NULL)
AND Test.[Test #] Not Like "6*"
GROUP BY Test.[Test #], Test.Test
ORDER BY Test.[Test #];


John W. Vinson[MVP]

 

[Guest]

I don't know how it works... I just know it works. That is absolutely
fantastic!

Thanks John.

Doug

I have joined the two tables and get the query to print exactly what I need.
But, when I add the "Date" field from the "Irv Tests" table with the
criteria... It stops printing the tests that were not performed.

Your setting a criterion on the [Irv Tests] date field is excluding
those records where the date field is NULL - which they will be if the
test was not performed. Try changing the HAVING criterion to a WHERE
criterion as well, to remove the 6* tests before doing the counting
(rather than counting them and then discarding the result:

SELECT Test.[Test #], Test.Test,
Count([Irv Tests].Test) AS CountOfTest
FROM Test LEFT JOIN [Irv Tests]
ON Test.[Test #] = [Irv Tests].[Test #]
WHERE (([Irv Tests].[Date]>=[Enter begining date]
And [Irv Tests].[Date]<=[Enter ending date])
OR [Irv Tests].[Date] IS NULL)
AND Test.[Test #] Not Like "6*"
GROUP BY Test.[Test #], Test.Test
ORDER BY Test.[Test #];


John W. Vinson[MVP]