Query problem

[rap43]

Access 2002 two tables, first table has student names, grades and image
number. The second table has Student names, grades and Student ID numbers.
Both tables have some of the some same names in each table and some that are
not. Each table has student with the duplicate exact name that are in
different grades or classes. I have the Student ID set as the primary key in
the Student ID table and the Image has the primary key in the image table. I
have a relationship between names in each table. I need to combine the
Student with IDs with the Students that have images.

Three queries, two that includes all fields of the both tables. Then a
combine query that has the Image fields from the image query and the Student
ID from the Student ID query.

The problem is that the same exact names will get the same Student ID went I
run the combine query. Even though they have different Student ID numbers in
the Student ID table is there a criteria I can set to avoid the problem?

 

[Jeff Boyce]

I'm having trouble visualizing what your tables are storing.

Are you saying that you use [Student Name] as the common field between the
tables? If so, what happens when you have two students named "John Smith"?

Are you saying that you record a [Student Name] and a [Grade], and repeat
that each time a student moves into a new grade (or are you describing
grades of "A", "B", ...)?

How can one [Student Name] have more than one [Student ID]?

More specific description of what you are storing may lead to more specific
suggestions for how to handle it...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[rap43]

I have two table with similar data they both have student name field and both
have a grade field. These are mostly the same student names some are
duplicate names. One table has the student’s ID number and the other has the
student’s image number. No student with the same name, in the same field IS
IN THE SAME GRADE. Yet went I run my query it will assign both student in the
output two ID number. Each will be in the output twice once with the correct
ID and once with the others ID.

I'm having trouble visualizing what your tables are storing.

Are you saying that you use [Student Name] as the common field between the
tables? If so, what happens when you have two students named "John Smith"?

Are you saying that you record a [Student Name] and a [Grade], and repeat
that each time a student moves into a new grade (or are you describing
grades of "A", "B", ...)?

How can one [Student Name] have more than one [Student ID]?

More specific description of what you are storing may lead to more specific
suggestions for how to handle it...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Access 2002 two tables, first table has student names, grades and image
number. The second table has Student names, grades and Student ID numbers.
Both tables have some of the some same names in each table and some that
are
not. Each table has student with the duplicate exact name that are in
different grades or classes. I have the Student ID set as the primary key
in
the Student ID table and the Image has the primary key in the image table.
I
have a relationship between names in each table. I need to combine the
Student with IDs with the Students that have images.

Three queries, two that includes all fields of the both tables. Then a
combine query that has the Image fields from the image query and the
Student
ID from the Student ID query.

The problem is that the same exact names will get the same Student ID went
I
run the combine query. Even though they have different Student ID numbers
in
the Student ID table is there a criteria I can set to avoid the problem?

 

[Jeff Boyce]

I must be short on caffeine today, but I'm still not visualizing what you
have and what you want to do.

Perhaps you could post the SQL statement of your query...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two table with similar data they both have student name field and
both
have a grade field. These are mostly the same student names some are
duplicate names. One table has the student's ID number and the other has
the
student's image number. No student with the same name, in the same field
IS
IN THE SAME GRADE. Yet went I run my query it will assign both student in
the
output two ID number. Each will be in the output twice once with the
correct
ID and once with the others ID.

I'm having trouble visualizing what your tables are storing.

Are you saying that you use [Student Name] as the common field between
the
tables? If so, what happens when you have two students named "John
Smith"?

Are you saying that you record a [Student Name] and a [Grade], and repeat
that each time a student moves into a new grade (or are you describing
grades of "A", "B", ...)?

How can one [Student Name] have more than one [Student ID]?

More specific description of what you are storing may lead to more
specific
suggestions for how to handle it...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Access 2002 two tables, first table has student names, grades and image
number. The second table has Student names, grades and Student ID
numbers.
Both tables have some of the some same names in each table and some
that
are
not. Each table has student with the duplicate exact name that are in
different grades or classes. I have the Student ID set as the primary
key
in
the Student ID table and the Image has the primary key in the image
table.
I
have a relationship between names in each table. I need to combine the
Student with IDs with the Students that have images.

Three queries, two that includes all fields of the both tables. Then a
combine query that has the Image fields from the image query and the
Student
ID from the Student ID query.

The problem is that the same exact names will get the same Student ID
went
I
run the combine query. Even though they have different Student ID
numbers
in
the Student ID table is there a criteria I can set to avoid the
problem?

 

[rap43]

I need to have the combined query output the correct Student ID from one
table with the correct image from the other table name in common in both
tables. Each table has names associate to an image and an ID number. The
problem is when you have two exact names. I have a field for grades also.

I must be short on caffeine today, but I'm still not visualizing what you
have and what you want to do.

Perhaps you could post the SQL statement of your query...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two table with similar data they both have student name field and
both
have a grade field. These are mostly the same student names some are
duplicate names. One table has the student's ID number and the other has
the
student's image number. No student with the same name, in the same field
IS
IN THE SAME GRADE. Yet went I run my query it will assign both student in
the
output two ID number. Each will be in the output twice once with the
correct
ID and once with the others ID.

I'm having trouble visualizing what your tables are storing.

Are you saying that you use [Student Name] as the common field between
the
tables? If so, what happens when you have two students named "John
Smith"?

Are you saying that you record a [Student Name] and a [Grade], and repeat
that each time a student moves into a new grade (or are you describing
grades of "A", "B", ...)?

How can one [Student Name] have more than one [Student ID]?

More specific description of what you are storing may lead to more
specific
suggestions for how to handle it...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Access 2002 two tables, first table has student names, grades and image
number. The second table has Student names, grades and Student ID
numbers.
Both tables have some of the some same names in each table and some
that
are
not. Each table has student with the duplicate exact name that are in
different grades or classes. I have the Student ID set as the primary
key
in
the Student ID table and the Image has the primary key in the image
table.
I
have a relationship between names in each table. I need to combine the
Student with IDs with the Students that have images.

Three queries, two that includes all fields of the both tables. Then a
combine query that has the Image fields from the image query and the
Student
ID from the Student ID query.

The problem is that the same exact names will get the same Student ID
went
I
run the combine query. Even though they have different Student ID
numbers
in
the Student ID table is there a criteria I can set to avoid the
problem?

 

[Jeff Boyce]

?... the SQL statement...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to have the combined query output the correct Student ID from one
table with the correct image from the other table name in common in both
tables. Each table has names associate to an image and an ID number. The
problem is when you have two exact names. I have a field for grades also.

I must be short on caffeine today, but I'm still not visualizing what you
have and what you want to do.

Perhaps you could post the SQL statement of your query...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two table with similar data they both have student name field and
both
have a grade field. These are mostly the same student names some are
duplicate names. One table has the student's ID number and the other
has
the
student's image number. No student with the same name, in the same
field
IS
IN THE SAME GRADE. Yet went I run my query it will assign both student
in
the
output two ID number. Each will be in the output twice once with the
correct
ID and once with the others ID.

:

I'm having trouble visualizing what your tables are storing.

Are you saying that you use [Student Name] as the common field between
the
tables? If so, what happens when you have two students named "John
Smith"?

Are you saying that you record a [Student Name] and a [Grade], and
repeat
that each time a student moves into a new grade (or are you describing
grades of "A", "B", ...)?

How can one [Student Name] have more than one [Student ID]?

More specific description of what you are storing may lead to more
specific
suggestions for how to handle it...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Access 2002 two tables, first table has student names, grades and
image
number. The second table has Student names, grades and Student ID
numbers.
Both tables have some of the some same names in each table and some
that
are
not. Each table has student with the duplicate exact name that are
in
different grades or classes. I have the Student ID set as the
primary
key
in
the Student ID table and the Image has the primary key in the image
table.
I
have a relationship between names in each table. I need to combine
the
Student with IDs with the Students that have images.

Three queries, two that includes all fields of the both tables. Then
a
combine query that has the Image fields from the image query and the
Student
ID from the Student ID query.

The problem is that the same exact names will get the same Student
ID
went
I
run the combine query. Even though they have different Student ID
numbers
in
the Student ID table is there a criteria I can set to avoid the
problem?

 

[rap43]

SELECT Image_Qry.Image, Image_Qry.Grade, Image_Qry.Name, Image_Qry.Hroom,
SubjectID_Qry.SubjectID
FROM Image_Qry INNER JOIN SubjectID_Qry ON Image_Qry.Name = SubjectID_Qry.Name
ORDER BY Image_Qry.Name;

?... the SQL statement...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to have the combined query output the correct Student ID from one
table with the correct image from the other table name in common in both
tables. Each table has names associate to an image and an ID number. The
problem is when you have two exact names. I have a field for grades also.

I must be short on caffeine today, but I'm still not visualizing what you
have and what you want to do.

Perhaps you could post the SQL statement of your query...

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have two table with similar data they both have student name field and
both
have a grade field. These are mostly the same student names some are
duplicate names. One table has the student's ID number and the other
has
the
student's image number. No student with the same name, in the same
field
IS
IN THE SAME GRADE. Yet went I run my query it will assign both student
in
the
output two ID number. Each will be in the output twice once with the
correct
ID and once with the others ID.

:

I'm having trouble visualizing what your tables are storing.

Are you saying that you use [Student Name] as the common field between
the
tables? If so, what happens when you have two students named "John
Smith"?

Are you saying that you record a [Student Name] and a [Grade], and
repeat
that each time a student moves into a new grade (or are you describing
grades of "A", "B", ...)?

How can one [Student Name] have more than one [Student ID]?

More specific description of what you are storing may lead to more
specific
suggestions for how to handle it...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Access 2002 two tables, first table has student names, grades and
image
number. The second table has Student names, grades and Student ID
numbers.
Both tables have some of the some same names in each table and some
that
are
not. Each table has student with the duplicate exact name that are
in
different grades or classes. I have the Student ID set as the
primary
key
in
the Student ID table and the Image has the primary key in the image
table.
I
have a relationship between names in each table. I need to combine
the
Student with IDs with the Students that have images.

Three queries, two that includes all fields of the both tables. Then
a
combine query that has the Image fields from the image query and the
Student
ID from the Student ID query.

The problem is that the same exact names will get the same Student
ID
went
I
run the combine query. Even though they have different Student ID
numbers
in
the Student ID table is there a criteria I can set to avoid the
problem?

 

[Jeff Boyce]

I don't know if it's causing your problem, but it can/will cause a problem
....

The word "Name" is a reserved word in Access. What Access thinks you mean
and what YOU mean are probably not the same.

First try renaming that field...

Regards

Jeff Boyce
Microsoft Office/Access MVP

SELECT Image_Qry.Image, Image_Qry.Grade, Image_Qry.Name, Image_Qry.Hroom,
SubjectID_Qry.SubjectID
FROM Image_Qry INNER JOIN SubjectID_Qry ON Image_Qry.Name =
SubjectID_Qry.Name
ORDER BY Image_Qry.Name;

?... the SQL statement...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to have the combined query output the correct Student ID from one
table with the correct image from the other table name in common in
both
tables. Each table has names associate to an image and an ID number.
The
problem is when you have two exact names. I have a field for grades
also.
:

I must be short on caffeine today, but I'm still not visualizing what
you
have and what you want to do.

Perhaps you could post the SQL statement of your query...

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have two table with similar data they both have student name field
and
both
have a grade field. These are mostly the same student names some are
duplicate names. One table has the student's ID number and the other
has
the
student's image number. No student with the same name, in the same
field
IS
IN THE SAME GRADE. Yet went I run my query it will assign both
student
in
the
output two ID number. Each will be in the output twice once with the
correct
ID and once with the others ID.

:

I'm having trouble visualizing what your tables are storing.

Are you saying that you use [Student Name] as the common field
between
the
tables? If so, what happens when you have two students named "John
Smith"?

Are you saying that you record a [Student Name] and a [Grade], and
repeat
that each time a student moves into a new grade (or are you
describing
grades of "A", "B", ...)?

How can one [Student Name] have more than one [Student ID]?

More specific description of what you are storing may lead to more
specific
suggestions for how to handle it...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Access 2002 two tables, first table has student names, grades and
image
number. The second table has Student names, grades and Student ID
numbers.
Both tables have some of the some same names in each table and
some
that
are
not. Each table has student with the duplicate exact name that
are
in
different grades or classes. I have the Student ID set as the
primary
key
in
the Student ID table and the Image has the primary key in the
image
table.
I
have a relationship between names in each table. I need to
combine
the
Student with IDs with the Students that have images.

Three queries, two that includes all fields of the both tables.
Then
a
combine query that has the Image fields from the image query and
the
Student
ID from the Student ID query.

The problem is that the same exact names will get the same
Student
ID
went
I
run the combine query. Even though they have different Student ID
numbers
in
the Student ID table is there a criteria I can set to avoid the
problem?