how to show all data in a report from two tables (1 Viewer)

[maramadan]

Hello
Please I need a little help, and sorry If I am asking in a wrong place in the forum

I have two tables in a database called school
Table 1 is called classes and have two fields (ID and Classname)
And have this date
1 class a
2 class b
3 class c
Table 2 is called students and have 3 fields (ID, Studentname and classname)
It has this date

[FONT=&quot]studentsID studentname class[/FONT][FONT=&quot]name[/FONT]
​

[FONT=&quot]3 [/FONT][FONT=&quot]bob class a[/FONT]
​

[FONT=&quot]4[/FONT] [FONT=&quot]zain [/FONT][FONT=&quot]class a[/FONT]

[FONT=&quot]5 robert class a[/FONT]
​

[FONT=&quot]6 ali class b[/FONT]
​

[FONT=&quot]7 sami class b[/FONT]
​

[FONT=&quot]8 hanz class b[/FONT]
​

As you can see there is no students in class c
I want to create a report that show me all the classes even if there are no students in them
Please find the attached database

Thank you in advance​

 

[Minty]

You need to use a query with a left join from the Class table to the Student table.

Click on the join in the query window and select "Show all records from Classes and only matching records from Students"

 

[Galaxiom]

Typically the developer would keep a table with a list of all classes matched with an ID. The ClassID in he student table is related.

An Outer Join from the Classes table to the Student table ensures that all classes are included in the recordset.

However you might consider that a Student will probably be in another class in the future. If you change the ClassID in their record you are destroying the information. Instead consider and Enrolments table which relates Students and Classes as a many-to-many relationship.

Enrolments would also contain an EnrolmentDate so each student has an enrolment in a particular class starting on any date you like. TerminationDate would also be relevant. This maintains the historical record of all enrolments.

 

[maramadan]

thank you , thank you, thank you very very mush