Solved

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

Posted on 2015-02-18
14
139 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 34

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 119

Expert Comment

by:Rey Obrero
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
 

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 119

Expert Comment

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

Author Comment

by:Hiken
ID: 40616832
Yes
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 200 total points
ID: 40616854
test this sample db
run Q_Test
Q-28619251.mdb
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 34

Expert Comment

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

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 stored procedures 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 Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now