If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I think I setup my table wrong???
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 |
#2
|
|||
|
|||
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 -- "M S" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Duane Hookom" wrote in message ... 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 -- "M S" wrote in message ... 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 |
#4
|
|||
|
|||
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 -- "M S" wrote in message ... 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 "Duane Hookom" wrote in message ... 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 -- "M S" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |