Display All Records

[Greg Ripper]

I have 2 tables tblGrades tblWeek7Test
The student information, names, period, grade, etc is in tblGrades.

The students take a test and their responses are stored in tblWeek7Test.

The problem is that not all the students take the test, absences, athletic
trips, etc.

How do I design a query to list all students by student id and include the
student answers of all the students who took the test and leave blanks for
all the kids that didn't?

Thanks as Always,

Rip

 

[Tom Ellison]

Dear Rip:

This would be a LEFT (or RIGHT) JOIN. If you are familiar with
creating JOINS, right click on the JOIN after you create it and select
Properties. There are 3 options listed. A bit of study, or
experimentation, should allow you to produce just what you're looking
for.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[KirSol]

How do I design a query to list all students by student id and include the

student answers of all the students who took the test and leave blanks for
all the kids that didn't?

Assuming you're using the query grid, here's what to do:

1. In the query grid, right-click the line that's joining your two tables
and choose "join properties".
2. In the resulting dialog box, change the join type to "Choose all records
from tblGrades and only those records from tblWeek7Test where the joined
fields are equal."

That will give you what's called an OUTER JOIN. You can peek at the
resulting SQL if you're curious.