Solved

Can I append data from one column to a single row in a table

Posted on 2015-02-18
14
148 Views
Last Modified: 2015-02-18
I have a query of Data that looks like this:  Each person can have a maximum of 10 scores  I would Like group by Name and append the data to a table that looks like(on the bottom).

Query
Name:                 Score:

Jones, Bud            85
Jones, Bud            87
Jones, Bud            89
Jones, Sara           90
Jones, Sara           92


Table

Name:                S1    S2    S3    S4    S5   S6    S7    S8   S9   s10

Jones, Bud        85     87    89    
Jones, Sara       90    92
0
Comment
Question by:Hiken
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 150 total points
ID: 40616650
Storing the data in an unnormalized fashion just makes it harder to work with.  If you want to make a report that shows the data the way it would look in a spreadsheet, then use a crosstab.  The wizard will help you build the query.  To make this generic, you'll need to add a sequence number to the table.
Query
 Name:          Seq:        Score:

 Jones, Bud   1           85
 Jones, Bud   2           87
 Jones, Bud   3           89
 Jones, Sara   1         90
 Jones, Sara   2         92

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40616663
try this query, change YourTable with the actual name of the table

TRANSFORM FIRST(Score) AS FirstScore
SELECT Name
FROM (
 SELECT YourTable.Name, YourTable.Score, "S" & COUNT(YourTable2.Score) AS Scores
 FROM YourTable, YourTable AS YourTable2
 WHERE YourTable.Score <= YourTable2.Score AND YourTable.Name = YourTable2.Name
 GROUP BY YourTable.Name, YourTable.Score
 ORDER BY YourTable.Name
) AS Q1
GROUP BY Name
PIVOT Scores
0
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 150 total points
ID: 40616677
As Pat said, you can easily do this with a Crosstab query but you need at least one other field.  For instance if you have a Date field in your table called DateTaken, the SQL of your crosstab query would look something like this:

TRANSFORM Sum(Score) AS SumOfScore
SELECT Table1.[FullName], Avg(Table1.Score) AS [Avg Score]
FROM Table1
GROUP BY Table1.[FullName]
PIVOT Format([DateTaken],"yyyy/mm/dd");


Notice that I replace Name with FullName, that's because Name is a reserved keyword.

Ron
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Hiken
ID: 40616795
I tried this but got this error:  "The Microsoft Access database does not recognize tTest.2Score as a valid field name or expression"  

TRANSFORM FIRST(Score) AS FirstScore
 SELECT FullName
 FROM (
  SELECT tTest.FullName, tTest.Score, "S" & COUNT(tTest2.Score) AS Scores
  FROM tTest, tTest AS tTest2
  WHERE tTest.Score <= tTtest2.Score AND tTest.FullName = tTest2.FullName
  GROUP BY tTest.FullName, tTest.Score
  ORDER BY tTest.FullName
 ) AS Q1
 GROUP BY FullName
 PIVOT Scores
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40616823
is the name of the field "Score" correct?
0
 

Author Comment

by:Hiken
ID: 40616832
Yes
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 200 total points
ID: 40616854
test this sample db
run Q_Test
Q-28619251.mdb
0
 

Author Comment

by:Hiken
ID: 40616919
Thanks Rey  for the help on this, but that is grouping by Score.  It works, but I need to see all 10 scores.  I did get the crosstab query to work that Pat and IroqSinta suggested.  I did add a sequence number.

Can I Sort the Scores across a row from low to high?



TRANSFORM First(tTest.Score) AS FirstOfScore
SELECT tTest.FullName, Avg(tTest.Score) AS Average
FROM tTest
GROUP BY tTest.FullName
PIVOT tTest.Sequence;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40616929
<but I need to see all 10 scores.>

populate the table with more records and you will see more columns.
0
 

Author Closing Comment

by:Hiken
ID: 40616942
I just got the sort  thanks to everyone for help on this.  I did try all the ways and they would all work depending on what you are looking for so I'll accept all solutions  Rey it would show me all ten columns as long as the scores were different.  Try typing in two scores the same.  When you run it you will only see one of the scores.

thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40616954
<Try typing in two scores the same.  When you run it you will only see one of the scores.>

you need a unique ID field to see all the scores even if they are the same
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40616962
That was why I ssugested adding a sequence number.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40617015
you don't need a sequence number field added to the table.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40617111
No, you can add it in the query if you want if the table has a uniqueID.  The point is to make consistent column heading names.  If you add the sequence number, you end up with columns named 1-10.  Without it you end up with a column named for each distinct value of Score.  I downloaded your example and I see that you did add a sequence number but the way you did it requires using Group By and that eliminates data that shouldn't be eliminated.  Without a unique identifier you can't assign a sequence number in a query.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question