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!

 

[SkipVought]

Hi,

You have TABLES in a DATABASE.

The RELATIONSHIP between on table and another table can be one to one or one to many.

Why not join the Student TABLE with your [Student Assignment] TABLE to generate the huge table, base on the school district, like, for instance, the Colonial School District which you may be familiar with.

Skip,

_[red][/red]

 

[lmccroskey]

Well, currently the tables are joined. There's over 100 tables actually and most of them are joined. The problem is that we want to take select records, move them to a server that we can make available to the Internet, and create a front end that schools can access to update student information.

My boss feels that the best, and easiest, way to do this is to have 1 flat database that contains all of the records. In the end that would be easiest. The problem is taking all of these records from the related tables, and putting them into a flat database.

 

[SkipVought]

"There's over 100 tables"

Could you post a list of what these are? Seems like a lot.

Skip,

_[red][/red]

 

[Andrzejek]

100 tables is already a headache, but turning them into one table will be a nightmare.

"schools can access to update student information"

I can not imagine how many required fields you will have to have for schools to do so.

I would strongly suggest to reconsider the 1 table approach.

Have fun.

---- Andy

 

[SkipVought]

Are you saying, for instance, that you have a table from each school district?

Do each table have SIMILAR data, but uses different column names and schema?

Skip,

_[red][/red]

 

[lmccroskey]

Here's a few of them. These are probably the most important for tracking purposes:

ADDRESSES, ADDRESSTYPES, ANTICIPATEDSERVICES, APSASSIGNMENTS, ASSESSMENTSANDACCOMODATIONS, ASSIGNMENTS, AVAILABLEASSESSMENTS, BUILDINGTYPES, CASELOADSTUDENTLIST, CHARTERASSIGNMENTS, CLASSROOMS, DAILYATTENDANCE, DISTRICTDATA, DISTRICTS, DROPOUTTYPES, EMPLOYEEASSIGNMENTS, EMPLOYEEFUNDINGSOURCES, EMPLOYEESOURCES, EMPLOYEES, EVALUATIONRESULTS, EXCEPTIONALITIES, EXITREASONS, GRADELEVELS, IEPHISTORY, INTERVENTIONLEVELS, INTERVENTIONLOCATIONS, LEADAGENCIES, LRES, MDEHISTORY, NATIVELANGUAGES, NORAHISTORY, NPSASSIGNMENTS, PSSAACCOMODATIONS, PSSAPARTICIPATION, REFERRALS, REFERRALSOURCES, RELATEDSERVICES, RELATEDSTUDENTSERVICES, RESIDENCETYPES, RESIDENTIALCODES, SCHOOLINGCODES, SCHOOLS, SECONDARYEXCEPTIONS, SERVICEBUILDINGS, SERVICEFUNDINGS, SERVICEPROVIDERS, SERVICETRANSPORTATIONS, STUDENTASSIGNMENTS, STUDENTEVALUATIONS, STUDENTEVALUATINTYPES, STUDENTS, SUSPENSIONS, TERMINATIONS, TRANSITIONPLANNING.

Those are the main tables. There are others used for reporting purposes, testing purposes, and other reasons. But for tracking purposes this list contains the important tables. There are about 15,000 kids and 50,000 assignments, so yes there's a lot of data and things to track.

 

[lmccroskey]

Yeah, making a flat table that emcompasses the data needed for 1 student's general information needs about 75 fields to be available. Give or take, since some students have more assignments and services than others. It's a huge headache, and I'm having trouble conveying this to the head honcho.

 

[lmccroskey]

Keep in mind, I don't need to turn all of those tables into a flat database. Those are just the tables that contain the data that I need. I have put together about 4 queries that extract the data needed. However, some of it is 1-to-1 and some of it is 1-to-many. So I can't use a simple Append query to just throw the data into a table. I'm trying to find a way to move through a 1-to-many record and put each record's data into corresponding fields.

Record1, Student1, Assignment1 needs to go to Record1, Student1, Assignment1 in the flat table.

Record2, Student1, Assignment2 needs to go to Record1, Student1, Assignment2 in the flat table.

Record3, Student1, Assignment3 needs to go to Record1, Student1, Assignment3... and so on and so forth until all of the Assignment records for that 1 student are done. Then move on to the next student.

 

[SkipVought]

You've got to be joking about putting everything into one table.

First of all, not ALL of the data is related. You have EMPLOYEE data that has nothing at all to do with STUDENT assignments. INTERVERTION? DROPOUTS? SERVICE...?????

There are four STUDENT tables. There may be more that are also related for STUDENT assinments. It is conceivable that THAT data could be JOINED and reported (QUERIED) I'm not sure I'd stick it all on one table, just query on the tables that are already there.

Skip,

_[red][/red]

 

[PHV]

Have a look here:
faq701-4233

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

 

[lmccroskey]

No... you don't understand the relationships. I've been working with this databse for a year now and I don't even understand them all. The EMPLOYEE table contains information for teachers, psychologists, physical therapists, and other staff needed for the students. Each student's ASSIGNMENT needs an EMPLOYEE. Each STUDENT has at least 1 ASSIGNMENT. An ASSIGNMENT is generated an IEP, with a STUDENT having multiple IEPs as the progress.

Try not to understand how the tables are related... trust me, they are. I'm just looking for a piece of code that can turn 3 records from one table/query, into 3 fields in a different table, with the StudentID being the unique identifier that is the key.

 

[SkipVought]

You have an extremely complex entity relationship task -- not one that will get you much help in a venue like this. This is Tek-Tips not Tek-we help you design a complex database reporting web system

you might consider hiring a consulting firm. What about the organization that designed the database for you?


Skip,

_[red][/red]

 

[Andrzejek]

Each STUDENT has at least 1 ASSIGNMENT.

How many ASSIGNMENTs can a STUDENT have?

If up to 20, then
you need 20 fields in your flat table
ElseIf there is no limit Then
you can not create one table
End If

I agree with SkipVought:
"You've got to be joking about putting everything into one table. "



Have fun.

---- Andy

 

[lmccroskey]

It's Tek-Tips,not Tek-We-Give-You-Attitude. If you can't help, that's fine. Thank you for viewing the post and giving it some brain-power. I genuinely appreciate it, as my own brain-power just isn't cutting it.

The entities are already related. I'm not asking for help with relating entities. I'm not sure why the focus is on the relationships so much. The relationships are already in place and I have made the queries to extract the data. I just can't quite figure out how to get multiple RECORDS from the same student into multiple FIELDS in a seperate table.

I, like others who have responded, am of the opinion that putting all of the data from multiple tables into a HUGE table is a bit much. Plus, it kind of defeats the entire purpose of a relational database. What can I do though? The boss wants the data flattened.

The route I would LIKE to use is MOVEFIRST, MOVENEXT to go through the recordset created from the queries, then turning each piece of data into a variable. For example, when the focus is moved to the 2nd record, then the second assignment would be called ASSIGNMENT2. I could then use a query to INSERT INTO the approriate location in the 2nd table. The problem I am having is knowing if the next record has the same STUDENTID as the previous record. If it does NOT have the same STUDENTID, then the data should be inserted into the 2nd table and the variables should be reset and populated with the next STUDENTID's data. If it DOES have the same STUDENTID, it should just continue populating variables with data.

PHV, I like that link about concatenating. I'm going to play around with that code and see if I can make something happen. Thanks!

 

[lmccroskey]

Andy, I'm looking to use only the top 3 assignments. No student has more than 3. However, there are up to 10 related-services. Yes, I was going to have to put up to 3 assignment fields and 10 services fields. It stinks.

 

[SkipVought]

How many ASSIGNMENTs can a STUDENT have?

If up to 20, then
you need 20 fields in your flat table
ElseIf there is no limit Then
you can not create one table
End If

I'm sorry that I disagree. I would not make a separate field for each assignment. I would have one or more fields like AssignmentTask, AssignmentDueDate, etc

Skip,

_[red][/red]

 

[lmccroskey]

Andy is right. Each ASSIGNMENT has many fields to it. Each assignment has an ASSIGNMENTID, so I would need 3 ASSIGNMENTID fields. Each assignment has a STARTDATE and STOPDATE, so I would need 3 STARTDATES and STOPDATES.

I would just use MOVENEXT to get to the 2nd ID and STARTDATE and whatnot, but as I said I can't figure out how to compare the next STUDENTID to the previous STUDENTID. If anyone has any ideas on that one, a big chunk of my worries would be dealt with. Thanks guys!

 

[lmccroskey]

Skip, just so you know, the consultants who made this monster charge $125 per hour. While me? I make a lowly $20. SO naturally, they want me to bang MY head against the wall over it. The problem is that if I was as good as the consultants at code and whatnot, I'd just quit and charge $100

 

[SkipVought]

dim PrevSID 

...
Do
...



   PrevSID = rst("StudentID")
   rst.mocrnext
loop

Skip,

_[red][/red]