I think I setup my table wrong???

[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

 

[Duane Hookom]

Congrats on recognizing your issue. You can use a union query to normalize:

SELECT ScoreID, 1 As SkillID, [SKILL1] as SCORE
FROM tblScores
UNION ALL
SELECT ScoreID, 2, [SKILL2]
FROM tblScores
UNION ALL
SELECT ScoreID, 3, [SKILL3]
FROM tblScores;

 

[M S]

Thanks for your help.

Do you have and more information that could possibly help? I have never
used a UNION query before.

1. Is there a link or resource I could read online to learn more?

.... or

2. I can provide a sample of the data (I used generic names in my original
post).

Thanks,
Mike

Congrats on recognizing your issue. You can use a union query to normalize:

SELECT ScoreID, 1 As SkillID, [SKILL1] as SCORE
FROM tblScores
UNION ALL
SELECT ScoreID, 2, [SKILL2]
FROM tblScores
UNION ALL
SELECT ScoreID, 3, [SKILL3]
FROM tblScores;


--
Duane Hookom
MS Access MVP
--

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

 

[Duane Hookom]

You can start with a basic select query with your table. You then switch to
SQL view and manually type in the "UNION ALL..." stuff.

--
Duane Hookom
MS Access MVP
--

Thanks for your help.

Do you have and more information that could possibly help? I have never
used a UNION query before.

1. Is there a link or resource I could read online to learn more?

... or

2. I can provide a sample of the data (I used generic names in my
original
post).

Thanks,
Mike

Congrats on recognizing your issue. You can use a union query to normalize:

SELECT ScoreID, 1 As SkillID, [SKILL1] as SCORE
FROM tblScores
UNION ALL
SELECT ScoreID, 2, [SKILL2]
FROM tblScores
UNION ALL
SELECT ScoreID, 3, [SKILL3]
FROM tblScores;


--
Duane Hookom
MS Access MVP
--

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