Data from more than one table

[Guest]

I have two tables in my database.
One called All Students and one called Disabilities.

My all students table lists all of my students and what subjects they are
doing etc

My Disabilities table lists only those students (from the all students
table) that have a disability and a description of the disability.

I want to create a report from the All students table to show all the
students & their information but I aslo want to include fields from the
Disabilites table on my report!!

Can anyone help me with how do this?

 

[Douglas J. Steele]

No offense, but it sounds as though your All Students table is incorrect.

Realistically, it sounds as though you should have 5 tables:
1. Students
2. Courses
3. Disabilities
4. intersection table to resolve the many-to-many between Students and
Courses
5. intersection table to resolve the many-to-many between Students and
Disabilities

In any case, given your two table, you need to create a table that joins the
tables together. You do this by creating a new query and adding both tables
to it. Drag the column(s) from the Students table onto the Disabilities
table to indicate the field(s) to join on. Select the line that appears when
you join the two tables and right-click. Select "Join Properties" from the
context-menu that appears. Select the radio button that corresponds to
"Include ALL records from 'Students' and only those records from
'Disabilities' where the joined fields are equal" Add the appropriate fields
from each table to the query and run it.

You should see one row for each row in the Students table. Those students
with disabilities should have the appropriate fields filled in, those
without disabilities should have Null in those fields.

 

[Van T. Dinh]

It sound like you need to create a Query using both Tables with a Left
(Outer) Join from the Table [All Students] to Table [Disabilities].

The Left Outer Join in the Query means that *all* Student records will be
selected and for those with related Disability records, show the relevant
Field values from the Table [Disabilities].

Check Access Help on Left Join (and also Inner Join to compare them).

 

[Steve Schapel]

1foxi,

Well, it probably would have been simpler to just include a field for
Disability in the All Students table, and dispense with the Disabilities
table. Unless, that is, you want to be able to record more than one
disability for a given student.

And I am suspicious about the design of your All Students table, where
you say "what subjects they are doing". Given that a student may be
doing more than one subject, you have a one-to-many relationship here,
and as such there probably should be a separate table for subjects taken.

Anyway, back to the main question ... How are the students
identified in the Disabilities table? It would be usual in such a case
that there is a student ID field, or registration number, or some such,
to uniquely identify each student in the All Students table, and then
this field is also included in the Disabilities table. You can then
make a Query which includes both tables, joined on their field in
common, and then your report can be based on this query. In this case,
you want the query to return all records from the All Students table,
regardless of whether there is a corresponding record in the
Disabilities table, so the join in the query would need to be a Left
Join. You achieve this in the query design view, by double-clicking the
join line between the tables, and select the appropriate option in the
Join Properties.