Counting fields in a table row

[Jeffrey Marks]

Hi

I got an Access database from the government. It's by no means optimal. They have a table of test scores that looks like this:

Student Number
MathTestScore2009-2010
ReadingTestScore2009-2010
MathTestScore2008-2009
ReadingTestScore2008-2009
(and then continued for earlier years)

my query needs to check all years and see if the score for each test (Math,reading, etc) is greater than 400. If it is, then I need to add 1 to the total tests passed. Otherwise not. Also if they passed the test in 2008-2009, the score for 2009-2010 will be blank/null because they no longer need totake the test once they pass.

Is there a way to do this using a table with the above format? Or should I put the time in (and time is very tight on this phase of the project) to optimize the database and then try this? What design would best suit these fields, by year or by test?

Thanks

Jeff

 

[John W. Vinson]

Hi

I got an Access database from the government. It's by no means optimal. They have a table of test scores that looks like this:

Student Number
MathTestScore2009-2010
ReadingTestScore2009-2010
MathTestScore2008-2009
ReadingTestScore2008-2009
(and then continued for earlier years)

my query needs to check all years and see if the score for each test (Math, reading, etc) is greater than 400. If it is, then I need to add 1 to the total tests passed. Otherwise not. Also if they passed the test in 2008-2009, the score for 2009-2010 will be blank/null because they no longer need to take the test once they pass.

Is there a way to do this using a table with the above format? Or should I put the time in (and time is very tight on this phase of the project) to optimize the database and then try this? What design would best suit these fields, by year or by test?

Thanks

Jeff

EEeeeeuwwww... your tax dollars at work. That's UGLY.

What you can do to extract the data into a tall-thin normalized structure is
a UNION query: something like

SELECT StudentNumber, "Math" AS Test, "2009-2010" AS Testyear,
[MathTestScore2009-2010] AS Score FROM yourtable
WHERE [MathTestScore2009-2010] IS NOT NULL
UNION ALL
SELECT StudentNumber, "Math" AS Test, "2008-2009" AS Testyear,
[MathTestScore2008-2009] AS Score FROM yourtable
WHERE [MathTestScore2008-2009] IS NOT NULL
UNION ALL
SELECT StudentNumber, "Reading" AS Test, "2009-2010" AS Testyear,
[ReadingTestScore2009-2010] AS Score FROM yourtable
WHERE [ReadingTestScore2009-2010] IS NOT NULL
UNION ALL
SELECT StudentNumber, "Reading" AS Test, "2008-2009" AS Testyear,
[ReadingTestScore2008-2009] AS Score FROM yourtable
WHERE [ReadingTestScore2008-2009] IS NOT NULL
UNION ALL
<etcetera through all the fields>

You can then base a totals query on this stored UNION query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com