Solved

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

Posted on 2015-02-18
14
145 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 35

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 35

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 35

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

856 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