Improve company productivity with a Business Account.Sign Up

x
?
Solved

Crosstab or pivot table? I want the data not the sum!

Posted on 2006-11-09
15
Medium Priority
?
381 Views
Last Modified: 2012-06-21
I currently have data in one format, and am not sure how to put it into another.

I have a table with records like this:

PupilID       Subject        Grade
1               English           A
2               English           B
3               English           A
4               English           C
1               Maths             D
2               Maths            E
3               Maths            A
4               Maths            A
1               French          B
2               French          B
3               French          C
4               French          A


What I want is a report / query that shows the data like so:

PupilID      English      Maths      French
1              A               D            B
2              B               E            B
3              A               A           C
4              C               A           A



I have tried pivot tables in Excel and Crosstabs in access to no avail.  They both want to perform a calculation (normally SUM) on the actual data (Grades).

Have I missed something obvious?  I have been puzzling over this for a while now.

Thanks in advance for any help with this one.

Richard.
0
Comment
Question by:highwaterhead
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17909641
Open up your crosstab in design view and change the sums shown in the total row to groupbys.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17909643
Put all fields in the graphical query editor. (PupilID       Subject        Grade)
Now change the querytype to crosstable (See Query menu)
There will appear a row with "GroupBy" there change the groupby under the Grade into MAX
Now set the PupilID to "Rowheader", the "Subject" to columnheader and the Grade to VAlue.

Run the query and check the outcome.

Nic;o)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17909787
You can use Max(), Min(), First(), Last() as there is only one number per Pivot
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'.

 
LVL 6

Accepted Solution

by:
gvlob earned 1000 total points
ID: 17909813
Here you go, put this in a new query in SQL view (Note I gave the table name as grade, change that to your real table name):

TRANSFORM First(grade.Grade) AS FirstOfGrade
SELECT grade.PupilID
FROM grade
GROUP BY grade.PupilID
PIVOT grade.Subject;
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17909941
Just like GRayL posted, my code uses the First().
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17909996
For that matter you can continue to use Sum() - as there is only one number per Pivot
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17910017
Wouldn't that give you an error since the data is text? I think you would get a type mismatch given the grades to be A, B, C, etc. The sum would work if it were a numeric value such as 4 (A), 3(B), etc.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17910042
Of course you're right.  I was just seeing if you would pick up on my deliberate mistake!
0
 

Author Comment

by:highwaterhead
ID: 17910051
Thanks for replies.
Nico5038 - I have done as suggested, and I do get a cross tab query, but not how I described as wanted. Your solution produces multiple rows per pupilID.
What I want is only one row per PupilID. eg;

PupilID    English    Maths    French
1             A            D            B

What I get is:

PupilID    English    Maths    French
1             A                        
1                         D          
1                                      B


Any further advice?
0
 

Author Comment

by:highwaterhead
ID: 17910069
Actually you are right.  Working with Text values is problematic
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17910085
Please try my query, it will work even with the letter grades.
0
 

Author Comment

by:highwaterhead
ID: 17910216
OK gvlob

I tried this with numerical data and it works a treat. I take it the "firstof" bit is the crucial bit.
I will test with text data in a moment.
Looking good.
0
 

Author Comment

by:highwaterhead
ID: 17910246
Great gvlob!
Mission accomplished.  Works with the text data as you predicted.
Thanks for help, will award points just now.

Richard.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17912176
Strange as the First() aggregate function is having a similar effect as the Max() I proposed.
Can you post the SQL you used so I can see what went wrong ?

Nic;o)
0
 
LVL 6

Expert Comment

by:gvlob
ID: 17914296
Highwaterhead, with numerical data you could use any of the functions given by GrayL, including the Sum.

Nico5038, I think that your solution should work also. From highwaterhead's remark it looks like he may have set the totals column for PupilID to something other than GroupBy. That's just my thinking though.
0

Featured Post

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'.

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.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

606 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