SQL Magic Needed

[penfold_99]

Hi All,

 

I am in need of some sql magic,

i have a table with the follow columns

 

teacher , subject ,english, maths, science

mr smith, science ,27 , 28 , 30

 

i would like to return the following

mr smith, science,30

 

is this possible with sql in access?

 

i would prefer doing this in sql and not vba.

[jamesb]

SELECT teacher, subject, CASE subject
 WHEN 'science' THEN science
 WHEN 'maths' THEN maths
 WHEN 'english' THEN english
 END
FROM table

 

That might do it. I assumed you didn't want a simple 'SELECT teacher, subject, science FROM table' query.

Edited June 11, 2009 by jamesb

[penfold_99]

thanks but there will be upto 20 subjects and i would like only one query.

[localzuk]

You need to normalise your database, storing the reference to a column within the same table as that column is not good practice from what I know.

 

Would it not be better to store the column with science/maths etc in it with an id linking it to the original table not be better? Then you can do a simple nested SELECT?

[jamesb]

Well, doing it that way would give you a list of teacher, subject, subject_mark for each teacher quite happily, but it would involve typing out each subject in the CASE statement. Can't find anything to simply pull the CASE statement out of the table itself but I'll keep looking.

 

(Note: Started typing this before reading localzuk's reply, so it has no bearing on that)

Edited June 11, 2009 by jamesb

[penfold_99]

hi thanks for your ideas.

 

it wont be possible to normalise the database as im pulling most of the data from anthor database which i dont have control and trying to make the best of a crap database.

 

i thought about a case statement but would be hard to keep upto date.

 

I think i will have to do it via vba.

[NotSoFast]

I'll start by saying I don't know the answer (!)

 

However, I wonder if it's possible to get the list of current subjects in the database using a SELECT DISTINCT query, then loop through the results of that running a second query for each subject returned. If so, that wouldn't need updating if the subjects changed.

 

I've never ever used them, but would stored procedures be able to handle this?

 

Article here that has the basics on stored procedures.

 

Just a thought.

[Guest monkeyx]

I have been putting of sorting out something like this in teh Serco Facility DB.

 

Anyhow this great article gives some pointers to how you might do something like this

 

Arrays and Lists in SQL Server 2005

[MattMitchell]

Is your source table stored in Access or MS-SQL? Probably the best way is to have a bit of VB code that will read in the un-normalised data and just populate a more normalised table (i.e. with the output in the form you're after) in code.