Query with common fields to all records

  • Thread starter Thread starter Prohock
  • Start date Start date
P

Prohock

I have a query that is collecting information from three tables. One table
has infromation about each student and another table is an incident table and
these tables are linked together using a student id number. The third table
is the school information that I want common to all fields in my query (this
data is only entered once for each school when the data base is first opened)
How do I link the school information data to every record in my query when
there is no common linked field?
 
You should add a field to the student table that can be used to link to the
school table. Is there some reason that this doesn't already exist?
 
I have added the field [School] in the student table and I want that field to
be the same as the one record that is entered in the school informaton table
that includes the [School] field. I don't want the user to have to enter the
school for every student, I want it to automatically put the school in for
each student.
 
Either create a form for the school and make the student form a subform of
that or, else, use a combo box to select the school for each student.

--
Lynn Trapp
Trainer/Application Developer


Prohock said:
I have added the field [School] in the student table and I want that field
to
be the same as the one record that is entered in the school informaton
table
that includes the [School] field. I don't want the user to have to enter
the
school for every student, I want it to automatically put the school in for
each student.

Lynn Trapp said:
You should add a field to the student table that can be used to link to
the
school table. Is there some reason that this doesn't already exist?
Click to expand...
Click to expand...
 
Prohock said:
I have a query that is collecting information from three tables. One
table has infromation about each student and another table is an
incident table and these tables are linked together using a student
id number. The third table is the school information that I want
common to all fields in my query (this data is only entered once for
each school when the data base is first opened) How do I link the
school information data to every record in my query when there is no
common linked field?
Click to expand...

You should probably do as the others suggested and add a school id to the
students table so the students are linked to the school. The only exception
is if you are tracking students' progress at multiple schools requiring a
many-to-many relationship. In that case you will need a 4th table to link
the schools to the students.
 
If I understand, you have ONE record in the school Information table. IF that
is the case, you don't need to join the school information table in the query
at all. If you have just ONE record and do not join the table then the values
from that table will show up on every row of the query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Prohock said:
...this data is only entered once for
each school when the data base is first opened)
Click to expand...

And then deleted before closing the database so there is never data for more
than one school in the database?
If so, see John's reply.
 
Back
Top