Drill-down Query Access 2007

[mapat]

Hello,

I have tables A(student information) and B(student yearly information) linked by studentID field.
I am trying to create a query in a drill-down manner that would display all fields from table A and when clicking on studentID on that same row it would display all fields from table B pertaining to that student linked by studentID.

Table A (tRecipient Students) has:
StudentID, FirstName, LastName, ...

Table B (tStudentYear) has:
StudentID, Year, ...

So far, I have easily joined both tables but it displays repeated rows for same studentID given that such student has several yearly entries in table B.
SELECT *
FROM [tRecipient Students] INNER JOIN tStudentYear ON [tRecipient Students].[Student ID] = tStudentYear.studentID;


Is there a way to create this drill-down type of query.

Your urgent help would be greatly appreciated.

Thank you

 

[arnelgp]

You can use a form and subform. The main form for student and the subform for the info. Create a master/child link on the subfirm using studentid.

 

[mapat]

Thank you for your reply, but I would need to do this using only tables and not forms since that is the business requirement.

Thank you.