Summing Columns into multiple Rows

[mabiem]

Hi All,

I have a linked table. The table looks like:


Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3

I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:


Course name | Course Grade
Student 1
Student 1
Student 2
Student 2

What is the best way to do this? I can not alwer the original table.

Mike

 

[pbaldy]

A Union query is one way of pulling the denormalized data into a normalized view:

SELECT Student, Course1Name, Course1Grade
FROM TableName
UNION ALL
SELECT Student, Course2Name, Course2Grade
FROM TableName

You may want to add a criteria to eliminate Null (empty) courses.

 

[mabiem]

So my linked table is called "CollegeCredit". The courses have 5 possible fields per course (name, college, course number, grade, credits) and these are unfortunately named:

Q83_1_TEXT(1) - for course 1 name
Q83_2_TEXT(1) - for course 1 college
Q83_3_TEXT(1) - for course 1 course number
Q83_4_TEXT(1) - for course 1 grade
Q83_5_TEXT(1) - for course 1 credits

There are 15 possible course entries with the number in brackets increasing by 1. So course two is:
Q83_1_TEXT(2) - for course 2 name
Q83_2_TEXT(2) - for course 2 college
Q83_3_TEXT(2) - for course 2 course number
Q83_4_TEXT(2) - for course 2 grade
Q83_5_TEXT(2) - for course 2 credits

If I try a simple SQL query like:
SELECT V4, Q83_1_TEXT(1), Q83_2_TEXT(1)
FROM CollegeCredit
UNION ALL
SELECT V4, Q83_1_TEXT(2), Q83_2_TEXT(2)
FROM CollegeCredit

I get "Undefined Function "Q83_1_TEXT" in expression.

The naming is unfortunate. I would like to remove nulls.

Let me know if you have any thought.

Thanks,

Mike

 

[mabiem]

I added quotes:

SELECT "V4", "Q83_1_TEXT(1)", "Q83_2_TEXT(1)"
FROM CollegeCredit
UNION ALL SELECT "V4", "Q83_1_TEXT(2)", "Q83_2_TEXT(2)"
FROM CollegeCredit;


Now the query loads but it just shows the column headers instead of data. The number of rows is correct though. I think I'm close...

 

[pbaldy]

Because of the inadvisable symbols, you'll need to bracket the names:

[Q83_1_TEXT(1)]

 

[mabiem]

Perfect! Thank you!

How would I go about removing nulls if I needed to?

 

[pbaldy]

Add a WHERE clause to each SELECT statement, on the appropriate field:

SELECT Student, Course1Name, Course1Grade
FROM TableName
WHERE Course1Name Is Not Null
UNION ALL
...

 

[mabiem]

Awesome. Perfect. Thank you so much.

 

[pbaldy]

No problemo!