not showing all records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 tables and 2 forms. students are required to take certain courses. I made a query off both tables. but it only gives me the records of those students who had the courses. I also need to know who didnt have the courses all on 1 report. Students is one table and courses the other. They are connected by the Student ID. Also the form for the courses is a subform if that matters
Thank you
Angel
 
Hello

In query builder, double click the line that joins the two tables, and select one of the choices that states something like "Select all records from Table1 and those from Table2 that match". (Normally choice 2 and 3). This will change the appearance of the joining line to indicate an arrow

Suppose you have the arrow pointing from table A to table B. Then all records in A will be shown, and if a record is found in B, that information is included. If no record is found in B, than all fields selected from B will be blank (Null)

In raw SQL this will be

SELECT A.*, B.* FROM A LEFT JOIN B ON A.ID = B.ID
 
THANK YOU THAT WORKE

----- Ivar Svendsen wrote: ----

Hello

In query builder, double click the line that joins the two tables, and select one of the choices that states something like "Select all records from Table1 and those from Table2 that match". (Normally choice 2 and 3). This will change the appearance of the joining line to indicate an arrow

Suppose you have the arrow pointing from table A to table B. Then all records in A will be shown, and if a record is found in B, that information is included. If no record is found in B, than all fields selected from B will be blank (Null)

In raw SQL this will be

SELECT A.*, B.* FROM A LEFT JOIN B ON A.ID = B.ID
 
I have 2 tables and 2 forms. students are required to take certain courses. I made a query off both tables. but it only gives me the records of those students who had the courses. I also need to know who didnt have the courses all on 1 report. Students is one table and courses the other. They are connected by the Student ID. Also the form for the courses is a subform if that matters
Thank you
Angel
Click to expand...

You may need an "Outer Join". Click the join line in the query design
window and select Option 2 (or 3) - "show all records in ..."

For a Form or Report, you may really want to use a Form with a Subform
(or Report/Subreport).
 
Back
Top