Recordsets and "one to many" tables

[LuigiX]

I have a recordset which I’m using a Do Loop on to extract records for each student and put into a spreadsheet. Problem is The student Id is in one table and the other values are in the “many” table joined on Student Id. My recordset is:

Set rstStudents1 = dbs.OpenRecordset("SELECT Students.FirstName, Students.FirstName, " _
'& " Students.FirstName, StudentUnits.UnitId FROM Students INNER JOIN " _
'& "StudentUnits ON Students.StudentId = StudentUnits.StudentId;", dbOpenSnapshot)

Question is how do I cycle through the recordset and extract the UnitIds for each student? Do I need 2 loops?

Thanks for your help.

 

[Rich]

Why can't you use a query?

 

[LuigiX]

HI Rich

I cant use a query and Access report although I'd like to. The client wants a spreadsheet populated from the database - who am I to argue?

 

[Pat Hartman]

You can use a query. You can export the query to excel by using the TransferSpreadsheet Method or Action.

 

[LuigiX]

Hi Pat

this is exiting. Where's the best place to go for help on this TransferSpreadsheet method? Do you have any code samples?

Cheers

Luigi

 

[LuigiX]

The transferspreadsheet method doesnt appear to give me the control over the way the information is displayed, display the units by student. The method just displays all records in full, line by line in the order it appears in the query.

this leaves the recordset route - correcto?

 

[Pat Hartman]

Isn't that what you asked for code to do?

 

[LuigiX]

Hi Pat

I probably did but what I really wanted was a way to sort the data out and put it into a spreadsheet in a certain way.

Actually I'm quite pleased with myself coz I created two recordsets with an inner and outer loop to give me this functionality.

I was left with the impression that TransferSpreadSheet was just too restrictive in terms of maipulating and formatting the data

Cheers

Luigi