SQL Help

carbon_13 · March 13th, 2008, 12:14 PM

Hello,
I need to make a query where I can sum up values in a row. I am wanting to setup it up using SQL.

My table is setup as such

__1 2 3 4 5 6
1 A B C A D C
2 A C D A B C
3 A B C A B C
4 A B F A C C
5 A B C A B C
6 A B C A B C

What I need it to do is sum up the row (not columns) and at the same time set values such as (A=4, B=3, C=2, D=1, F=0).
Any ideas on how to do this. Thanks

Carbon_13

mmcdonal · March 13th, 2008, 12:30 PM

I can help you do this with code, but I am not sure how to approach it using just SQL.

mmcdonal

Look it up at: http://wrox.books24x7.com

carbon_13 · March 13th, 2008, 02:09 PM

I know basic SQL, but not enough to assign values to letters, and to sum rows instead of columns with the appropriate value.
carbon_13

Carbon_13

crritchie · March 13th, 2008, 03:26 PM

This doesn't look like something that should even be done in access; it looks like an excel spreadsheet. What's it for?

carbon_13 · March 13th, 2008, 06:19 PM

Yes, it is easier to do it in excel (I have made a working macro in vba that does this) but since I have all my data in
Acces I need to go ahead and do this as well in Access. I will end up making a report using this data. I am new to SQL, any advice helps. I is basically to calculate GPA from letter grades.
carbon_13

Carbon_13

woodyz · March 13th, 2008, 08:15 PM

There are several ways to do this. Here is one.

You need a lookup table for the grade values. Let's call this "ValueLookup".

It will contain two columns. One for the grade letter (column named TheKey), and one for its matching value (column named TheValue).

It will contain a row for each Grade in your system:

A 5
B 4
C 3
D 2
E 1

Or whatever.

Then, for each column in your grades table, you need to do a subquery and add up all the values for a row. It would look something like this (this example just uses 3 columns for brevity):

Code:

SELECT ((SELECT a.TheValue From ValueLookup a  Where a.TheKey = b.Col1) 
                  + (SELECT a.TheValue From ValueLookup a  Where a.TheKey = b.Col2) 
                 + (SELECT a.TheValue From ValueLookup  a  Where a.TheKey = b.Col3)) AS RowTotal
FROM Grades AS b;

The result will be a table of rows with a single column in each row named RowTotal with a value that is the sum of all the "looked up" grades for that row. Make sense?

Of course, you need to use the column and table names that are in your database.

Cheers,
Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit

carbon_13 · March 17th, 2008, 01:24 PM

Yes this is perfect. But my problem is that lets say that column 2 (Col2) has different values for A B C D. How would I implement that into this summation to take into account that the values for column 1 and 2 are different. For simplicity lets say that the other column had that same value for A B C D as does column 1 but only column 2 is the one with different values of A B C D.
Thanks for the help
carbon_13

Carbon_13

woodyz · March 17th, 2008, 08:02 PM

You are describing a case where you need a lookup for each different set of values. In your example, you would need one lookup for column 2 with relevant values for the keys in that column, and one lookup for all other columns.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems
My blog... please visit