Columns to Rows - Normalizing Tables

[marysilvaramos]

Hi Everyone:

I have reviewed many post about converting columns to rows and still does not get it. :banghead:As you might imaging, I am new using access. Please, need help. Here is what I would like to do:

I have a table which want to normalize. Table Name: FlatFile This is the fields list:
StudentID
SchoolQuarter
Subject1
TeacherSubject1
GradeSubject1
PercentageSubject1
Subject2
TeacherSubject2
GradeSubject2
PercentageSubject2.....

Subject20
TeacherSubject20
GradeSubject20
PercentageSubject20

I want to move the data from that tablet to the following: Table Name: tablenormalized; Field List:

StudentID
SchoolQuarter
Subject
Teacher
Grade
Percentage

The process needs to be done automatically every school quarter. It is needed to create the reports cards and I plan to use the crosstab query with the tablenormalized.

Thanks in advance!

Mary

 

[plog]

It is needed to create the reports cards and I plan to use the crosstab query with the tablenormalized

I'm all for normalization, but it sounds like the output is perfectly aligned with the non-normalized table. What do you hope to gain from putting it into tablenormalized? Are you running other reports from this? Are you trying to make data entry easier? What problem are you having with the non-normalized table?

 

[marysilvaramos]

Thanks, Plog to replying.

The number of subjects change per student, currently the max is 11 subjects per student, but there are some students that have only 8 or 9. The school plans to add more subjects but are not sure how many or when. So, almost half of the table is empty. In the future, they will requests statistics about the grades, gpa, percentages, etc, etc. I think that the second table will more easy to manage to provide the information required.

They want a report card that can be read horizontally. Like this:
Quarter 1/ Quarter 2 / Quarter 3 / Quarter 4
Subj1 Grade1Q1/Perc1Q1 Grade1Q2/Perc1Q2 Grade1Q3/Perc1Q3 Grade1Q4/Perc1Q4
Subj2 Grade2Q1/Perc2Q1 Grade2Q2/Perc2Q2 Grade2Q3/Perc2Q3 Grade2Q4/Perc2Q4
.
.
.
Subj20 Grade20Q1/Perc20Q1 Grade20Q2/Perc20Q2 Grade20Q3/Perc20Q3 Grade20Q4/Perc20Q4

Should appear Average per class and total GPA.

Using the second table (normalized), i think would be more easy to use a crosstab query to create the report card.

What do you think?

Thanks again,

Mary

 

[marysilvaramos]

I forgot to add that I received the data from a .csv file extracted from a website. The db read it and place it in a table in the db with the same format as the .csv file. There is no data entry needed.

Thanks

Mary

 

[plog]

Thanks for the explanation and yes, a normalized table is the way to go. Your tablenormalized is correctly set up to do this.

As to how to get your data from the current structure into tablenormalized depends on if you will have to do this more than once or not. How will future data get into this database? Will it come in the form of FlatFile, or will it be input directly into the database through forms?

 

[marysilvaramos]

I received the data from a .csv file extracted from a website. The db read it and place it in a table in the db with the same format as the .csv file. There is no data entry needed.

thanks

 

[plog]

Yes, you've stated that. How will future data get into the database?

 

[marysilvaramos]

same way! it is a procedure that is run when it is needed.

Thanks

Mary

 

[plog]

In that case I wouldn't use a table, instead I would 'normalize' your data via a UNION query and use that query as your datasource. Essentially it would be a query that takes the raw data and structures it like your tablenormalized.

This is what that union query would look like:

SELECT StudentID, SchoolQuarter, Subject1 AS Subject, TeacherSubject1 AS Teacher, GradeSubject1 AS Grade, PercentageSubject1 AS Percentage
FROM FlatFile
UNION ALL
SELECT StudentID, SchoolQuarter, Subject2 AS Subject, TeacherSubject2 AS Teacher, GradeSubject2 AS Grade, PercentageSubject2 AS Percentage
FROM FlatFile
UNION ALL
SELECT StudentID, SchoolQuarter, Subject3 AS Subject, TeacherSubject3 AS Teacher, GradeSubject3 AS Grade, PercentageSubject3 AS Percentage
FROM FlatFile
UNION ALL
...

You would continue adding SELECT statements until you have accounted for every Subject number. By doing this instead of putting it into a new table, you can load your FlatFile table and everything will automatically work without any processing or updating.

 

[marysilvaramos]

i will try it and let you know! thanks plog!

 

[marysilvaramos]

Happy New Year, Plog!

I am working on the code you sent and received the following message:

" Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE' "

Here is what I wrote:

SELECT StudentID,Col1_Grade_Period, Col1_Class_Name_1 AS Subject, Col1_Teacher_1 AS Teacher, Col1_Grade_1 AS Grade, Col1_Percentage_1 AS Percentage
FROM ReportCard
UNION ALL
SELECT StudentID, Col1_Grade_Period, Col1_Class_Name_2 AS Subject, Col1_Teacher_2 AS Teacher, Col1_Grade_2 AS Grade, Col1_Percentage_2 AS Percentage
FROM ReportCard
UNION ALL;

Thanks in advance!

Mary

 

[plog]

Remove the last UNION ALL. It's essentially means 'AND'. Since there's no more SELECT statements after it, there's no need for that last one.

 

[marysilvaramos]

BEAUTIFUL!!!! Thanks a ton!

Mary