Convert some data in column to rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys,

i m a begineerin Access. Recently been task to do some Access quries. The
example goes like this:

Change from-
Name Subject Grades
John English 4
John Chinese 5
John Math 2
Mary English 3
Mary Math 4
Alex English 3
Alex Math 1
Alex Science 2

To-
Name Subject Grade Subject1 Grade Subject2
Grade
John English 4 Chinese 5 Math
2
Mary English 3 Math 4
Alex English 3 Math 1
Science 2

Is there anyway of doing this~? If not using access, any other windows
office(eg. Excel) might be able to do this?~

Thank for the help~
Jason
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That result set would be difficult. Ask the client if they would be OK
w/ a format like this:

Name Chinese English Math Science
John 5 4 2
Mary 3 4
Alex 3 1 1

To get this format you can use a crosstab query:

TRANSFORM Sum(Grades) AS DaValue
SELECT Name
FROM table_name
WHERE < your criteria >
GROUP BY Name
PIVOT Subject

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjfqvoechKqOuFEgEQIPmgCgyyCrKgNUFs2VGAfE606GNtz8X30AnROs
KbQIa56fWicerQVo4f8BU/Hp
=uVPF
-----END PGP SIGNATURE-----
 
Back
Top