Can't run the query I need... table setup wrong?

  • Thread starter Thread starter M S
  • Start date Start date
M

M S

SCOREID SKILL1 SKILL2 SKILL3
1 100 60 100
2 60 60 100
3 100 100 60

I have the table above. I think I set it up wrong, and it should be
something like this:

SCOREID SKILLID SCORE
1 1 100
1 2 60
1 3 100
2 1 60
2 2 60

My issue is that I have designed so many input screens around how it is
setup now, that I really can't go back and start from scratch.

Is there a query that I can do or run take will extract all the field names
and get them as values, so I can more easily report on the table? i.e., I
need a crosstab with each SKILL down the left and the average as the value.

See what I am saying? Thanks for your help!

-Mike
 
You don't want to hear this, but I'll say it anyway. Start over. You'll
spend 5x the amount of time trying to fit the square peg into the round
hole.

$0.02, don't spend it all in one place.
 
Is there a query that I can do or run take will extract all the field names
and get them as values, so I can more easily report on the table? i.e., I
need a crosstab with each SKILL down the left and the average as the value.
Click to expand...

You can migrate this existing data into a normalized table with what's
called a "Normalizing Union Query". You need to go into the SQL window
(the query grid isn't able to handle it). Something like:

SELECT ScoreID, (1) AS SkillID, [Skill1] AS SCORE
FROM yourtable
WHERE Skill1 IS NOT NULL
UNION ALL
SELECT ScoreID, (2), [Skill2]
FROM yourtable
WHERE Skill2 IS NOT NULL
UNION ALL
SELECT ScoreID, (3), [Skill3]
FROM yourtable
WHERE Skill3 IS NOT NULL;

Save this UNION query, check that the results look correct, and then
base a MakeTable or Append query upon it.

John W. Vinson[MVP]
 
Back
Top