Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 13th, 2008, 12:14 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Help

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
__________________
Carbon_13
 
Old March 13th, 2008, 12:30 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old March 13th, 2008, 02:09 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 13th, 2008, 03:26 PM
Registered User
 
Join Date: Mar 2008
Location: Cedar City, UT, USA.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This doesn't look like something that should even be done in access; it looks like an excel spreadsheet. What's it for?
 
Old March 13th, 2008, 06:19 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 13th, 2008, 08:15 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 17th, 2008, 01:24 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 17th, 2008, 08:02 PM
Friend of Wrox
 
Join Date: May 2006
Location: San Diego, CA, USA.
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
creating ssis packagte for sql server to sql serer Laxmikant_it ASP.NET 3.5 Professionals 0 November 26th, 2008 12:23 AM
Converting from MS SQL 2005 to Sql Epress edition saif44 SQL Language 0 February 16th, 2007 04:17 PM
Failed to copy objects from SQL server to SQL Serv monfu SQL Server 2000 4 December 4th, 2005 05:54 PM
Move SQL DB from one sql to another sql server Israr SQL Server 2000 3 January 24th, 2005 02:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.