Help me dive into the deep end of MS Access

Factual Questions
1

I am a teacher. I have been using Excel for years to manage data from assessments. I use Excel for lots of things and can make it do pretty much everything I want.

Now there is a change in the way that we do assessments and I need something more powerful. I suspect Access is my new friend. Essentially, what I need is to make my data array 3-D.

Let’s see if I can describe what I want.

I have a number of assessment criteria that will be used throughout the year. For example:
1.1 makes woobles
1.2 evaluates woobles
2.1 interprets secondary zoigs
2.2 demonstrates zoig categorisation
2.3 zoig boonification

I have a number of assessment tasks that will be undertaken throughout the year. Not every assessment will assess every assessment criteria. For example
assess1 includes 1.1, 2.1, 2.3
assess2 includes 1.1, 1.2, 2.2, 2.3
assess3 includes 2.2
assess4 includes 2.1, 2.2, 2.3

Have a class full of students and need to record a grade for each student for each assessment in each applicable assessment criteria. I will also need to have formulas to calculate summary grades in each assessment criteria at the end of each reporting period.

My initial thought was to use Excel but this would mean creating a separate sheet for each student to reflect the 3-d nature of the data. This would be a very cumbersome way to go especially when entering results.
There will be times when it will be necessary to view or edit data student by student. Other times when I will want to view or edit by assessment, and still further occasions when I will want to analyse according to the assessment criteria. I understand that Forms are my saviour here – each of these situations could view as a 2-D table.
I have been wading through various online Access How-to guides. So far they have set things out as a 2-d table and have not shown me anything useful that I can’t already do with Excel. I am keen to bypass as much superfluous guff as possible and get into the details of what will actually be useful to me. I think I lack the terminology to describe what I want and this makes searching for help difficult.
So, can anyone help me by either providing advice or pointing me to a good online guide.
J.

2

Are you sure you have to invent this yourself?

From a quick search, it looks like this free web gradebook service will do what you want without the need to design it yourself (check out the demo in the lower right). You can browse/edit by person, by assignment, or by semester averages.

Also, if this is actually for a class, check if your school already uses Moodle or similar software (which have integrated gradebooks).

3

Yep. There are good reasons to do it myself.

The main difference between the gradebook service you link to and what I want is that I need to be able to assign a number of different grades for each assessment piece – perhaps as many as 10 in some cases.

I would like to be able to design it to the particular needs of my school.

If I am able to create a good template then it can be used across several departments by a large number of staff.

We already have reporting software but it is not up to this task. We also have Access but no one uses it. (Isn’t that the same just about everywhere?)

Finally, it wouldn’t hurt to add some Access skills to my bag of tricks.

4

You might have a look at FileMaker Pro. I always found it easier to use than Access.

5

It sounds like you need Tables Of Keys.

That is, your assessment tasks could be recorded in a table (conceptually) like this:
assess1 includes 1.1
assess1 includes 2.1
assess1 includes 2.3
assess2 includes 1.1
assess2 includes 1.2
assess2 includes 2.2
assess2 includes 2.3
assess3 includes 2.2
assess4 includes 2.1
assess4 includes 2.2
assess4 includes 2.3

You’d actually probably want another table of assessments that looked like this:
1, Assess1
2, Assess2
3, Assess3
4, Assess4
5, AssessBanana
etc

Then your table of keys (the first example in my post) would only refer to assessments by their IDs:

1, 1.1
1, 2.1
1, 2.3
2, 1.1
2, 1.2
2, 2.2
2, 2.3
3, 2.2
4, 2.1
4, 2.2
4, 2.3

And when AssessBanana needs to include zoig boonification, you just add a record:
5, 2.3

6

Thanks for your thoughts.
It probably makes more sense to make use of software we currently have than try to introduce something new. I am sure that Access is up to it.

A table of keys sounds like it might be a workable structure.

I am working through some youtube videos at the moment which seem to be going in the right direction. However, I am on my sixth 10 min tutorial and we have only just started setting up the fields. The danger of skipping bits is that it is possible to jump over the exact bit you need. At this point I am still uncertain of the best way of setting up my tables.

Anyway, keep the ideas coming.
J.

7

Ok, I don’t use Access, but I’m pretty good at SQL Server, and let me see if I can design it out for you.

You need 3 tables, which you’ve pretty much already spelled-out, and 1 joining table to use for your one-to-many relationship. (1 assesmentTask has many assessmentCriteria.)

So I’d build something like this (hopefully this notation makes sense):

TABLE assessmentCriteria
id (autonumber),
code (in your example 1.1, 1.2, 2.1 etc)
description (in your example “makes woobles”, “evaluates woobles” etc)

TABLE assessmentTask
id (autonumber)
name (in your example, “assess1”, “assess2”, etc)

Now here’s the tricky part. Since each Student needs a score for each “assessmentTask + assessmentCriteria” combination, we need a table to represent that criteria, which will look like this:

TABLE assessment
id (autonumber)
assessmentCriteriaId (foreign key to assessmentCriteria.id)
assessmentTaskId (foreign key to assessmentTask.id)

Ok, so we have only two more tables, one for Student, and one to link each Student to their many assessments:

TABLE student
id (autonumber)
name, age, sex, whatever

TABLE studentAssessment
id (autonumber)
studentId (foreign key to student.id)
assessmentId (foreign key to assessment.id)
score (their grade/rating/whatever)

Phew. Now you have all the tables you need, and should be able to represent as many students, assessments, as you need.

BTW, the process of converting your data from a 2D table to multi-dimensional relationships is called “normalization”. I believe what I just posted is “third-normal form”. That should help your searching.

8

I quickly diagrammed it out in SQL Server, and I think this is what you’re looking for. In any case, I think the image makes it 100 times easier to understand what’s going on and how the tables join with each other.

9

You should be able to do useful things with either Access or any version of SQL Server. – Any SQL database engine will do. Access uses SQL internally, and you can write your queries directly using SQL for tasks more complicated than the standard drag-and-drop GUI can do for you.

You can even use this idea to help you learn SQL. Note that one of the Views available is “SQL View”, which allows you to view and edit the SQL generated by the GUI screens. Or you can even write your own from scratch.

Access has an interesting SQL extension called a Pivot Table, which is very handy for creating some kinds of reports. The SQL code for this is a bit arcane – This was one example where I figured out how to write it by viewing the SQL code that the GUI auto-generated. When you get into writing your own reports, you might find it useful to study these. (Whether or not you get into the business of writing your own SQL from scratch.)

Access also comes with another important feature for the non-programmer (i.e., the typical end-user or casual designer, like you, who isn’t a professional SQL programmer): THE USER INTERFACE. Right there in Access, you can design your tables, queries, reports, and run them, and get a variety of nicely-done reports, all from a point-and-click style user interface. Especially if lots of people in your department (besides just you) will be using this, you need this or something equivalent.

Microsoft SQL Server comes with its own elaborate user interface (based on Visual Studio), but it’s much more oriented toward the SQL designer/programmer. This is not the interface that your casual end-users (other than yourself, or maybe including yourself) would be able to use easily.

There is a small-scale version of Microsoft SQL Server that you can download for free, meant for use on a single machine or small local networks. It is a full-featured SQL Server, but stripped down and optimized for use by a small number of users (meaning dozens rather than thousands). It used to be called MSDE (Microsoft Database Engine or something like that). I think it’s not called that anymore, so you’ll need to do a bit of research to find out what it’s called now.

There are also other non-Microsoft SQL servers out there, some of which are freeware. Two notable ones are MySQL and PostgreSQL. These are primarily Linux applications, but are available for Windows too (at least I think MySQL is). There is also Oracle SQL Server – they might have a stripped-down freeware version. (The last time I tried it, many years ago, it was waaaaaay too slow to be useful.)

10

Blakeyrat. You are a legend. I probably won’t get a chance to work on this for a day or so, but this certainly looks like what I need. I won’t know fore sure until I do some playing and probably some learning.
Thanks so much.

Can you explain what is meant by the term “foreign key”?

11

Blakeyrat almost has it right. You want to add “CREATE” in front of “TABLE…”, and Access SQL syntax is a little different than in SQL server (see below). You can find good syntax references online for MySQL, which is what Access uses. You may also want to look into something that teaches basic database design. I thought I could learn it through brute force application to a need, but there are a lot of things I wouldn’t have figured out unless I learned them first in an on-line course I took.

Going back to Blakeyrat’s first table: you can cut and paste this into a SQL window in an Access query:

CREATE TABLE AssessCriteria
(ID INTEGER PRIMARY KEY,
Code DOUBLE,
Description VARCHAR(30)
);
Once the table is created, you will have to go into table design and change the ID field type to “autonumber”. Foreign keys are fields in other tables that create a unique identifier for the record in another table. For instance, you have a table that lists names and phone numbers, another table listing cities and states, and a third table that lists company names. If you create a fourth table that pulls the name, city and company from the other three tables into a single record, the name, city and company in the first three tables are foreign keys in the fourth table.

12

I’m rusty on Access SQL, but . . . I never knew it was the same as MySQL. Pretty much ALL implementations of SQL are different in lots of minor ways, and some not-so-minor ways. Also, there must certainly be some keyword you can add into that (ID INTEGER PRIMARY KEY, line to make it auto-numbers, so you don’t have to do it separately afterward. After all, everything you do with the GUI gets build into some SQL command which is then executed. And you don’t need to write the above SQL statement at all. You could do the whole thing with the GUI. (I prefer writing SQL directly. Then I know what’s going on.)

A foreign key is a field in some table that refers to the primary key in some other table.

Suggested reading: Find reference material on one-to-one relationships, one-to-many (or many-to-one) relationships, and many-to-many relationships. If you study these topics, especially the many-to-many, you will have a good clue about building 2-D or 3-D or 10-D tables and about using primary and foreign keys.

13

If you really want to learn Access I suggest signing up at UtterAccess.

Very nice folks who have a VAST amount of Acces knowledge.

14

It’s obviously psuedo-code, not SQL.

15

Hm, good question… foreign key is one of those things that’s hammered into you so much in your SQL course that you stop thinking of it as anything other than “foreign key”.

Basically, it’s how you relate two tables together. So you’ll notice that in each of the tables I created, they have an autonumber column named “id”. This is known as the “primary key”. The word “key” there is the same way it’s used in other fields of computer science when talking about “key/value”, the “key” serves to identify a specific value. (In this case, the value is the row in the database.) It’s “primary” because this is the key that exists purely to identify the value and for no other reason. (In SQL, you can have more than one key in a table. For example, social security number might be a secondary key to identify a particular American, but you wouldn’t want to use it as a primary key as some Americans have no SSN and some SSNs have multiple Americans.) It’s autonumber because each value in this key column needs to be unique, but other than that we don’t really care what the value is. So we tell the database to just “use the last value, but add one” when you add new records to it.

A foreign key is a value that refers to a primary key in some other table. You define foreign keys for a few reasons:

  1. The most important is so you can “join” the two tables into one combined result, using the JOIN clause of your SQL query.
  2. Another important reason is so the database can do some basic error-checking. If your foreign key refers to record 5555 but that primary key doesn’t exist, that’s an error. I don’t know about Access, but SQL Server will prevent you from inserting foreign keys that have no cooresponding primary key, which serves to protect from data-entry errors.
  3. The query optimizer can use it as a “hint” for how to run the query more efficiently.

I hope that helps.

Remember that SQL administration is considered a “career” and not a “hobby”. There’s a lot to learn, so keep that in mind and don’t be discouraged if this doesn’t make sense right away.