Display of 1 to Many Relationship's Records in DBGRID

[Shella]

Hi,

I've fully completed my project but the only flaw is I dunno how
to retrieve a list of
1 to Many relationship's records in DBGRID.
For a simple example as listed below
----------------------------------
ID Name Age Course |
----------------------------------
01 John 19 MS ACCESS
01 John 19 MS WINDOWS
01 John 19 MS POWER POINT
02 Joe 20 MS ACCESS
02 Joe 20 MS PUBLISHER
03 WAYE 21 VB
03 WAYE 21 PASCAL


ID, Name, Age is in Student Table, Course is in
Course Table, the relationship of these two tables
is 1 to Many in which a student can enrol in many courses
as possible. As I use Data Control and the following method

DataControl.RecordSource = SQL
DataControl.Refresh
(DBGrid is bound to DataControl).
I get the result as shown above, there're
7 records being called by DataControl and listed in DBGRID.

My point is, how can I get the outcome as shown below, where the fields
in Student Table just occur once no matter how many courses the
student enrol in, and the DataControl will show there're only 3 (based
on the number of ID occurence) records but NOT 7 (based on the number
of Course occurence) anymore.
----------------------------------
ID Name Age Course |
----------------------------------
01 John 19 MS ACCESS
MS WINDOWS
MS POWER POINT
02 Joe 20 MS ACCESS
MS PUBLISHER
03 WAYE 21 VB
PASCAL
----------------------------------------


Is my idea possible in VB ? Please and Thank you very much, eagerly
waiting for solution.

[Lothar Haensler]

I'd use the Hierarchical FlexGrid that comes with VB 6. It has explorer like open and close bitmaps for displaying hierarchical relationships.

[Ravi Kiran]

If you have VB 6.0, go for Lothar's suggestion. Works w/o having to write code.

Since you did not mention the VB version, here is some idea, for which you need to write code yourself!.Obviously no-coding effort of DbGrid wont do for your case.

Take a Listview or MSFlexGrid (asuming you have VB 5).
1. Create one record set which will fetch all the students from Students table.
2.for one student, create another record set, which will contain all the courses he takes.
3.Add the 3 items from Students record as one row into LV/FlexGrid.
4.Then iterate thru all the records of the 2nd recset, and add the course name only in the 4th column. If you are using Listview, use subitems, and leave the Text and 2 subitems blank. Using Flexgrid use TextMatrix. This trik will make it appear as you want.
5.Repeat step 2,3,4 for all students of recset of step 1


RK