Group(?) report in Access

[joylee]

ID Lname SName
1 Davis Science 5
1 Davis Math 4
1 Davis Language 5
2 Smith Science 3
2 Smith Language 5
2 Smith Math 2
3 Cohen Language 5
3 Cohen Science 3
3 Cohen Math 4
3 Cohen Art 4

ID Lname Activity
1 Davis Piano
1 Davis Gym
2 Smith Violin
2 Smith Swimming
2 Smith Dance
3 Cohen Piano
3 Cohen Reading
3 Cohen Basketball

Dear,
I have two tables shown above.
How can i make a report showing like:

Student Davis

Subjects List
Science 5
Math 4
Language 5

Activity list
Piano
Gym

Student Smith

Subjects List
Science 3
Language 5
Math 2

Activity list
Violin
Swimming
Dance

This pattern repeats to each student.
Thank you for your attention.
Joung

 

[rondeondo]

Hi Joung
Are you the owner/designer for this database?
If so, it's best to have a table for activities, classes, students. You then have a table that links the students to their classes and another the link the students to their activities.
Student table:
StudentID, FName, SName
Activity Table:
ActivityID, Desc, any other fields
Subject table:
subjectID, Desc etc

Student-Class:
rowID, studentID,subjectID

Student-Activity:
rowID, studentID, activityID

Then any reports use the relevant tables. This means each student info is only entered once in the database rather than being entered separately in the class and activity tables.
Then if you want a list of classes you can just refer to the class table, same with activities. It's possible from your current layout but it's less efficient.

From your current, to get a list of classes you need to use something like:
SELECT DISTINCT SName FROM <first table name>
Distinct makes the query return only one line for each value.

 

[joylee]

Dear Rondeondo,

Thank you. Yes , I am the the owner of the DB, and I have all the tables, and queries you mentioned.
But still I cannot have what I want.
I want to, for one student (sorting I do not mind), list up all the subjects she/he is taking, then list up all the activities the student is doing.
Then move to another student.
Joung

 

[xenou]

In a general sort of a way I think you want to create a report with subreports - actually two subreports. It's not easy to explain how to create reports in Access in a post. You probably will do best by searching for examples online of creating access reports (it is very common to have table relationships like this, with a something on a "One" side and something else on the "Many" side - Student/Classes, Student/Activities, Customer/Orders, Manager/Employees and things like that. I would imagine once you get the first part figured out (Student/Classes) then you can apply the same knowledge to add the second part (Student/Activities).

 

[rondeondo]

I agree with xenou. To make a report with two sub reports is a little fiddly but the way to work your way up is to make one that list the subjects per student, a second that lists the activities per student, then the third is just a report of a page per student, then add the first two as sub-reports.
For the sub reports you'll want to trim them down to just the data without report headers and without page headers. Some searching should find some tutorials about how to do this in detail

 

[joylee]

Dear,

It sounds very possible.
O.K., I will try the sub reports.
Thank you.
Joung