merging rows

[BurleyRegistrar]

I have an Excel spreadsheet that lists students and their classes so that
each class is displayed on a separate row with the student name repeated at
the start of each row:

Name LangArts Math Science Soc Stud
JOHN DOE Algebra 1
JOHN DOE LA 7 Adv
JOHN DOE
SocSt 7 ST
JOHN DOE LiveSci7
JANE SMITH Math7 ST
JANE SMITH LA 7 ST
JANE SMITH
SocSt 7 ST
JANE SMITH Earth Sci

What I'd like is one row per student listing the class they have under each
heading. Is this doable in Access? I tried to figure out how to do it in
Excel and just can't do it.

 

[BurleyRegistrar]

Name LangArts Math Science
JOHN DOE Algebra 1
JOHN DOE LA 7 Adv
JOHN DOE
JOHN DOE LifeSci7
JANE SMITH Math7 ST
JANE SMITH LA 7 ST
JANE SMITH
JANE SMITH Earth Sci


When I saw my post I noticed that the "page" wasn't wide enough for all my
columns so I've edited it (above) to eliminate the Social Studies column.

 

[tedmi]

Yes, it is possible in Access, but you must forget about your spreadsheet
mindset. You will need three tables:
Table Student:
StudentID, FirstName, LastName, other info about the student (NOTHING about
courses)

Table Course:
CourseID, Course Desc, other info about course (NOTHING about students)

Table StudentCourse:
StudentID, CourseID, Semester, Grade, other info about this student in this
course.

You can use these to create two kinds of reports:
Student: Joe Blow
Course1
Course2

Student: Jane Doe
Course3
Course4

or if you need:
Joe Blow: Course1, Course2

you can create a cross-tab report.

 

[BurleyRegistrar]

I think I should have been more specific about what data I'm trying to
compile.

For example using my illustration below, JOHN DOE took Algebra 1 for Math,
LA 7 Adv for Language Arts and LifeSci7 for Science.

And, actually,for each course I've merged data into one column for each that
shows the class level and the final grade the student earned - like Algebra
1: A, LA 7 Adv: B, etc.

So I need a report with rows that show:

JOHN DOE Algebra 1: A LA 7 Adv: B LifeSci 7: A+
JANE SMITH Math 7 ST: C LA 7 ST: B- Earth Sci: D

 

[KARL DEWEY]

Using tedmi table structure and this query --

TRANSFORM First([CourseDesc] & " - " & [Grade]) AS Expr1
SELECT [firstName] & " " & [LASTNAME] AS Name
FROM Course INNER JOIN (student INNER JOIN StudentCourse ON
student.studentNo=StudentCourse.StudentID) ON
Course.CourseID=StudentCourse.CourseID
GROUP BY [firstName] & " " & [LASTNAME]
PIVOT Course.Field;
Results looks like this ---
Name LangArts Math Science Soc Stud
Jenifer Aldridge LA 7 ST - A Algebra 1 - A Earth Sci - C LifeSci 7 - C
Peggy Wolley LA 7 Adv - B+ Math 7 ST - B Earth Sci - C LifeSci 7 - C+

 

[Larry Daugherty]

You're missing the point of some huge differences between Access and
Excel. They aren't just confused versions of each other. They are
significantly different platforms designed to address significantly
different aspects of information manipulation and management. Don't
be confused by the fact that an Access table datasheet display looks a
lot like an Excel spreadsheet.

Relational Database Management Systems (Access et alia) have some
fairly rigorous rules about data organization. Excel does not.

One of the early items on the list of things to do when creating a new
Access application is to analyze the prospective application and to
identify the entities in play. In your case that would be at least
Students, Courses, StudentsCourses as sketched for you by tedmi. Note
that every entity of a given type belongs in the one table defined for
that type. Once the schema is defined the higher level elements,
Forms & Reports & their queries, are easily achievable.

FWIW it seems possible that you could achieve the desired outcome
within Excel using a cross-tab query; you couldn't approach the
elegance of a well crafted Access solution but you'd have your
information in a terse format rather quickly.

HTH

 

[mahotiere joseph]

You're missing the point of some huge differences between Access and
Excel. They aren't just confused versions of each other. They are
significantly different platforms designed to address significantly
different aspects of information manipulation and management. Don't
be confused by the fact that an Access table datasheet display looks a
lot like an Excel spreadsheet.

Relational Database Management Systems (Access et alia) have some
fairly rigorous rules about data organization. Excel does not.

One of the early items on the list of things to do when creating a new
Access application is to analyze the prospective application and to
identify the entities in play. In your case that would be at least
Students, Courses, StudentsCourses as sketched for you by tedmi. Note
that every entity of a given type belongs in the one table defined for
that type. Once the schema is defined the higher level elements,
Forms & Reports & their queries, are easily achievable.

FWIW it seems possible that you could achieve the desired outcome
within Excel using a cross-tab query; you couldn't approach the
elegance of a well crafted Access solution but you'd have your
information in a terse format rather quickly.

HTH