Records missing when running a query

[Guest]

I have a database with student records and the classes they are enrolled
into. I have a query with the following SQL coding:

SELECT DISTINCT [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[First Name], [Student in
Courses].[Last Name], Teachers.[Teacher Name], [Student in Courses].[Course
Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.[Section ID] = [Student in Courses].[Section ID]) AND (Courses.ID =
[Student in Courses].ID)) ON (Teachers.ID = [Student in Courses].[Teacher
ID]) AND (Teachers.ID = Courses.[Teacher ID])
WHERE (((Teachers.[Teacher Name])=[Enter Teacher Name:]))
ORDER BY [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[Last Name];

When I input the name Johnson for teacher, 2 of her classes- approximately
40 records do not show up. Any suggestions as to what is wron?
Thanks in advance.

 

[Ken Snell [MVP]]

Other than the data aren't there? < g >

Try changing the INNER JOIN to LEFT JOIN throughout the query. With the
query you have here, if any of the tables does not have a record in it,
there will be no record for that teacher or class or whatever in your
output.

 

[Guest]

I tried changing the SQL to Left instead of Inner and it wouldn't allow it. I
checked the tables involved and the records clearly show acurately. Any other
suggestions?

Other than the data aren't there? < g >

Try changing the INNER JOIN to LEFT JOIN throughout the query. With the
query you have here, if any of the tables does not have a record in it,
there will be no record for that teacher or class or whatever in your
output.
--

Ken Snell
<MS ACCESS MVP>

I have a database with student records and the classes they are enrolled
into. I have a query with the following SQL coding:

SELECT DISTINCT [Student in Courses].[Section ID], [Student in
Courses].ID,
Courses.[Course Title], [Student in Courses].[First Name], [Student in
Courses].[Last Name], Teachers.[Teacher Name], [Student in
Courses].[Course
Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.[Section ID] = [Student in Courses].[Section ID]) AND (Courses.ID
=
[Student in Courses].ID)) ON (Teachers.ID = [Student in Courses].[Teacher
ID]) AND (Teachers.ID = Courses.[Teacher ID])
WHERE (((Teachers.[Teacher Name])=[Enter Teacher Name:]))
ORDER BY [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[Last Name];

When I input the name Johnson for teacher, 2 of her classes- approximately
40 records do not show up. Any suggestions as to what is wron?
Thanks in advance.

 

[Guest]

First, verify that the Courses.[Teacher ID] isn't somehow different for these
two courses. Check the Courses table's records and verify that they have the
correct Teacher ID for these two courses. If so and it's a text field, check
for leading spaces, trailing carriage returns, et cetera.

If that's okay, then check the [Student in Courses].[Teacher ID] for a
difference with these 40 records. Ensure that they have the correct Teacher
ID. If it's a text field, check for leading spaces, trailing carriage
returns, et cetera.

Next, if the tables aren't normalized and allow duplicates, check for
alternate spellings of the name, such as Johnston, Jonson, Johnsen, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

I checked for all of those anomalies and also ran it for other teachers. This
occurred for a few more teachers as well but consistently not all. This is
getting weirder and weirder. Is it a problem to try to put in fields from
different tables- if the tables are linked?

First, verify that the Courses.[Teacher ID] isn't somehow different for these
two courses. Check the Courses table's records and verify that they have the
correct Teacher ID for these two courses. If so and it's a text field, check
for leading spaces, trailing carriage returns, et cetera.

If that's okay, then check the [Student in Courses].[Teacher ID] for a
difference with these 40 records. Ensure that they have the correct Teacher
ID. If it's a text field, check for leading spaces, trailing carriage
returns, et cetera.

Next, if the tables aren't normalized and allow duplicates, check for
alternate spellings of the name, such as Johnston, Jonson, Johnsen, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have a database with student records and the classes they are enrolled
into. I have a query with the following SQL coding:

SELECT DISTINCT [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[First Name], [Student in
Courses].[Last Name], Teachers.[Teacher Name], [Student in Courses].[Course
Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.[Section ID] = [Student in Courses].[Section ID]) AND (Courses.ID =
[Student in Courses].ID)) ON (Teachers.ID = [Student in Courses].[Teacher
ID]) AND (Teachers.ID = Courses.[Teacher ID])
WHERE (((Teachers.[Teacher Name])=[Enter Teacher Name:]))
ORDER BY [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[Last Name];

When I input the name Johnson for teacher, 2 of her classes- approximately
40 records do not show up. Any suggestions as to what is wron?
Thanks in advance.

 

[Ken Snell [MVP]]

Wouldn't allow it? I assume you were in SQL view when you tried to make this
change?

Switch to Design view, and right-click on each of the join lines. Select
Edit and then select the option that shows all records from Teachers table
(I assume that that is the "master" table for this query).

--

Ken Snell
<MS ACCESS MVP>

I tried changing the SQL to Left instead of Inner and it wouldn't allow it.
I
checked the tables involved and the records clearly show acurately. Any
other
suggestions?

Other than the data aren't there? < g >

Try changing the INNER JOIN to LEFT JOIN throughout the query. With the
query you have here, if any of the tables does not have a record in it,
there will be no record for that teacher or class or whatever in your
output.
--

Ken Snell
<MS ACCESS MVP>

I have a database with student records and the classes they are enrolled
into. I have a query with the following SQL coding:

SELECT DISTINCT [Student in Courses].[Section ID], [Student in
Courses].ID,
Courses.[Course Title], [Student in Courses].[First Name], [Student in
Courses].[Last Name], Teachers.[Teacher Name], [Student in
Courses].[Course
Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.[Section ID] = [Student in Courses].[Section ID]) AND
(Courses.ID
=
[Student in Courses].ID)) ON (Teachers.ID = [Student in
Courses].[Teacher
ID]) AND (Teachers.ID = Courses.[Teacher ID])
WHERE (((Teachers.[Teacher Name])=[Enter Teacher Name:]))
ORDER BY [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[Last Name];

When I input the name Johnson for teacher, 2 of her classes-
approximately
40 records do not show up. Any suggestions as to what is wron?
Thanks in advance.

 

[Guest]

Still having the problem described above. Records are still missing. HELP!!

I checked for all of those anomalies and also ran it for other teachers. This
occurred for a few more teachers as well but consistently not all. This is
getting weirder and weirder. Is it a problem to try to put in fields from
different tables- if the tables are linked?

First, verify that the Courses.[Teacher ID] isn't somehow different for these
two courses. Check the Courses table's records and verify that they have the
correct Teacher ID for these two courses. If so and it's a text field, check
for leading spaces, trailing carriage returns, et cetera.

If that's okay, then check the [Student in Courses].[Teacher ID] for a
difference with these 40 records. Ensure that they have the correct Teacher
ID. If it's a text field, check for leading spaces, trailing carriage
returns, et cetera.

Next, if the tables aren't normalized and allow duplicates, check for
alternate spellings of the name, such as Johnston, Jonson, Johnsen, et cetera.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have a database with student records and the classes they are enrolled
into. I have a query with the following SQL coding:

SELECT DISTINCT [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[First Name], [Student in
Courses].[Last Name], Teachers.[Teacher Name], [Student in Courses].[Course
Status], [Student in Courses].Comments
FROM Teachers INNER JOIN (Courses INNER JOIN [Student in Courses] ON
(Courses.[Section ID] = [Student in Courses].[Section ID]) AND (Courses.ID =
[Student in Courses].ID)) ON (Teachers.ID = [Student in Courses].[Teacher
ID]) AND (Teachers.ID = Courses.[Teacher ID])
WHERE (((Teachers.[Teacher Name])=[Enter Teacher Name:]))
ORDER BY [Student in Courses].[Section ID], [Student in Courses].ID,
Courses.[Course Title], [Student in Courses].[Last Name];

When I input the name Johnson for teacher, 2 of her classes- approximately
40 records do not show up. Any suggestions as to what is wron?
Thanks in advance.