Solved

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

Posted on 2015-02-18
14
143 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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