Turn 1-to-many tables into 1 huge flat table. 2

[lmccroskey]

I'm trying (unsuccessfully) to put together code that will basically turn a '1-to-many' database and convert it into a flat database. Here are the basics:

A query is run that will find all of the students within a particular school district, and put them into a table. The unique identifier is the StudentID. There are tons of other fields associated with this query, but since all of the data in the Students table is 1-to-1, this will simply be an append query.

The next part is where I'm having trouble. Each student has many assignments associated to them in the Assignments table, and each assignment has many fields. For example, student 1 has 3 assignments, with each assignment being a record. I need to take each assignment and place it into the corresponding field in the flat table. For example, record1 (assignment #1)from table 'A' will be placed into the Assignment1 field in table 'B'. Then record2 (assignment #2) from table 'A' will be placed into the Assignment2 field in table 'B'.

I made a query that will look at every student ID generated from the very first query listed above, and then find the corresponding assignments in the Assignments table. So i can turn this into a recordset. However, when I movenext, I don't know how to take the data in that 2nd record and put it into the the corresponding fields in the flat table.

Any help would be GREATLY appreciated!

 

[PHV]

Another way is to use 2 recordsets:
an outer loop browsing the students
an inner loop browsing the assignments

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lmccroskey]

Dude! *slaps-self-in-face* I think that's it!

DIM PrevID

DO WHILE PrevID = StudentID

PrevID = rst("StudentID")
...
...
rst.mocrnext

I might have to buy you a drink Skip! I been staring at this nightmare for so long that I'm overlooking the little things, like DO WHILE loops!

 

[SkipVought]

<aside>lmccroskey, I notce that the SERVER of your ISP is in PA. Are YOU? That's why I mentioned the Colonial School District, of which I am an alumnus, some 47 years ago</aside>

Skip,

_[red][/red]

 

[lmccroskey]

Correct. I'm in Indiana, PA actually. However, Colonial is not one of the districts that we service. I work for an Intermediate Unit actually, which is probably why you got mislead when you heard things like ASSIGNMENT. Basically, we help with special-needs kids. So an ASSIGNMENT in this case is being assigned to a class for speech or hearing impaired.

 

[SkipVought]

Western PA. The Colonial SD is near Philly.

Do you have a GUI to query the data, like an MS Access QBE grid?

Skip,

_[red][/red]

 

[lmccroskey]

For testing I just made a simple form with a button that executes an SQL query. The finished product is going to be an Active Server Page, and I think that's why it needs to be flat. Honestly, I don't know anything about ASP, but my boss does. He was saying that the ASP can only work from 1 data source, such 1 table. That's why it needs to be flat

 

[PHV]

He was saying that the ASP can only work from 1 data source
So, I don't think he does know ASP so well ...

 

[SkipVought]

He doesn't know his ASP from...

WHAT?

Skip,

_[red][/red]

 

[lmccroskey]

P.S. Thank you guys, Skip and PHV. I actually did a little of both of your ideas. I used a DO...WHILE loop, that made use of 2 recordsets, a source and destination. Thanks to both of you guys for your ideas and input! Thread closed!