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

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
HikenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
test this sample db
run Q_Test
Q-28619251.mdb
0
 
PatHartmanConnect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
IrogSintaConnect With a Mentor Commented:
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
 
HikenAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
is the name of the field "Score" correct?
0
 
HikenAuthor Commented:
Yes
0
 
HikenAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<but I need to see all 10 scores.>

populate the table with more records and you will see more columns.
0
 
HikenAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
PatHartmanCommented:
That was why I ssugested adding a sequence number.
0
 
Rey Obrero (Capricorn1)Commented:
you don't need a sequence number field added to the table.
0
 
PatHartmanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.