• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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

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
highwaterhead
Asked:
highwaterhead
  • 5
  • 4
  • 3
  • +2
1 Solution
 
puppydogbuddyCommented:
Open up your crosstab in design view and change the sums shown in the total row to groupbys.
0
 
nico5038Commented:
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
 
GRayLCommented:
You can use Max(), Min(), First(), Last() as there is only one number per Pivot
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gvlobCommented:
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
 
gvlobCommented:
Just like GRayL posted, my code uses the First().
0
 
GRayLCommented:
For that matter you can continue to use Sum() - as there is only one number per Pivot
0
 
gvlobCommented:
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
 
GRayLCommented:
Of course you're right.  I was just seeing if you would pick up on my deliberate mistake!
0
 
highwaterheadAuthor Commented:
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
 
highwaterheadAuthor Commented:
Actually you are right.  Working with Text values is problematic
0
 
gvlobCommented:
Please try my query, it will work even with the letter grades.
0
 
highwaterheadAuthor Commented:
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
 
highwaterheadAuthor Commented:
Great gvlob!
Mission accomplished.  Works with the text data as you predicted.
Thanks for help, will award points just now.

Richard.
0
 
nico5038Commented:
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
 
gvlobCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now