Beginner trying to create something similar to a PivotTable

[alexllap]

Hello everyone! I'm somewhat of a beginner here, but I'm pretty comfortable with Excel (formulas, macros, pivot tables, etc.), and I've been doing this process for the longest time in Excel, but it takes a lot of time, and someone suggested I try using Access.



So a little context: I work at a university and have about 200 students that I'm responsible for helping navigate their way through our program. It's often very helpful to see at a glance what courses students have taken and what they're currently registered for in a given quarter to see where gaps may exist in terms of having enough seats for everyone in an upcoming quarter/course. I usually download three pretty large tables. Most of these have something like 50 columns, but I'll give some very basic examples:


1. All current students and their general information

Row Count	StudentNo	Name
1	111111	John Smith
2	222222	Kevin Jones
3	333333	Alicia Gray

2. All courses that students have ever taken and their grades

Row Count	StudentNo	Name	YrQtr	crsAbbrev	crsNumber	crsGrade
1	111111	John Smith	2015-4	ENGL	100	2.7
2	111111	John Smith	2015-4	CHEM	142	2.8
3	111111	John Smith	2015-4	ENGL	101	3.3
4	222222	Kevin Jones	2016-1	STAT	101	2.8
5	222222	Kevin Jones	2016-1	PHIL	120	4.0
6	333333	Alicia Gray	2016-1	ENGL	100	3.0
7	333333	Alicia Gray	2016-2	ENGL	200	3.1
8	333333	Alicia Gray	2016-2	BIOL	100	3.3

3. All courses that students are currently registered for

Row Count	StudentNo	Name	YrQtr	crsAbbrev	crsNumber	crsGrade
1	111111	John Smith	2019-2	ENGL	200
2	111111	John Smith	2019-2	ENGL	201
3	222222	Kevin Jones	2019-2	ENGL	100
4	222222	Kevin Jones	2019-2	CHEM	110
5	333333	Alicia Gray	2019-2	ENGL	201

I have it set up so that in the first table, the primary key is the student number (since students won't show up twice on that table). On the second and third tables, I'm using the Row Count as the primary key. The only relationships I have are from the first table's primary key (student number) to the student number in the two other tables.

I'm hoping to create a query/report that will give me something like this, showing the grades the students have earned and also if they're currently registered for the course (IP).

Row Count	StudentNo	Name	ENGL 100	ENGL 101	ENGL 200	ENGL 201
1	111111	John Smith	2.7	3.3	IP	IP
2	222222	Kevin Jones	IP
3	333333	Alicia Gray	3		3.1	IP

*When I've been doing this in Excel, instead of it showing "IP", it would show the YrQtr they're registered.


One of the difficult things I've run into is how our institution reports the academic term (in the YrQtr field). So right now, we're in spring quarter, which is the second quarter of the year, hence 2019-2. So 2015-5 would be Autumn 2015, and 2016-1 would be Winter 2016.

Any and all help is greatly appreciated!

[June7]

What you want is a CROSSTAB query. Would have to concatenate the crsAbbrev and crsNumber fields to serve as the ColumnHeaders.

Student Name should not be in the dependent tables. This is duplication of data.

Use the query builder. Start with a SELECT query that joins tables to gather the necessary fields. Switch to CROSSTAB and assign fields as appropriate for row and column headers and value. Value would be YrQtr field using Max() function.

Look for tutorial if need more info.

Access used to have a "pivot" mode for tables and forms but I think that has been dropped.

[Micron]

It may look like a "row count" but you have to stop thinking of it that way.
http://www.utteraccess.com/wiki/Autonumbers
http://www.fmsinc.com/free/newtips/primarykey.asp
As for duplication of data, if you use tblStudentID (autonumber, PK) as the link between it and tblRegistered (my name) wherever you now have 111111 you would instead have 1 from tblStudents. So you repeat 1 everywhere other than tblStudents rather than 111111. While that may seem inconsequential, consider what happens if Mary Smith gets married and becomes Mary Jones. Your design better propagate the new name everywhere otherwise you've got trouble. Linking values shouldn't be done unless they are guaranteed not to change, but even then it's dubious. While I'm at it, be sure that the policy is that a student who drops out and comes back a few years later keeps the same number or you'll be faced with another problem.

If you want a report in the end I'm not seeing a need for a crosstab at all. Methinks you would group on student ID and list courses one of several ways as you see fit; e.g.
- all with a grade if completed, then those not (thus are registered but not complete as there's no grade)
- all with a grade and another report for those not completed
- first case, but sub report for registered
- additional groupings in one report (by student, by grade = yes, by course with no grade

Lastly, advise to NOT store year and quarter together in the same field, especially using special characters in data. That usually means no apostrophes in names either.

[alexllap]

Thank you! As far as the year and quarter being in the same field, besides manually separating these, is there any other relatively easy way? At my institution this is the standard way of reporting the academic term.

[June7]

Since the structure of the YrQtr value is simple and consistent, I don't think it is critical to separate them in table. They can easily be separated with calculations in query when needed.

As Micron alluded to, apostrophes (and quote marks) in data can cause issues when executing SQL statements in VBA procedures. But this can be dealt with when needed (I have had to). I've never encountered issue with other characters in data.

[Micron]

From what I see, if the query says "show me x for the year 2018" - you have to parse it to get the year or provide all 4 possibilities for a given year.
Some days concatenated and calculated fields are OK, others not, I guess.

EDIT:

besides manually separating these, is there any other relatively easy way?

Sorry I forgot to answer that. Should you choose to do so, you'd use an update query to make a Yr field (not Year) = Left(YrQtr,4) and a Qtr field Right(YrQtr,1). I would leave the yrqtr field intact if that's the standard for the data.

[June7]

Yes, separate fields would likely be easier and if OP has control over table structure and how data is entered they can decide whether or not to modify.

However, such a simple value can be easily parsed.

SELECT *, Val([YrQtr]) AS Yr, Right([YrQtr],1) AS Qtr FROM table WHERE Val(YrQtr) = 2018;