Access 2000: How do I use data to create field names?

[Jen Pollard]

Hi, everyone

I've been reading the list for a couple of days to try to get a feel
for how things work. There're some really knowledgeable people here,
and I'm hopeful that someone can help me with this problem.

I'm attempting to create a new markbook for exams... We're currently
using a series of about 40 Excel spreadsheets with something along
the lines of 88 pages worth of macro coding (excel 4 macro coding,
too. It's that old!) Normally, I would think that was a relatively
easy job, but it's become a bit complicated right at the beginning!

First, some background... (long - sorry!)

We have two parts to our course (part 1 and part 2 - original, I
know... and each part has several classes or 'papers' - 12 in part
1, for example. Each paper also has several faculty members who mark
scripts and dissertations within it. Examiners can mark several
papers and which examiner is marking which paper is known beforehand.
Which student is marked by which examiner is, for all intents and
purposes, random. So - each paper will have a mark entering sheet
with the list of candidates down the left and across the top, the
examiner names plus a few other fields (each examiner has to have a
rubric field - this is a code given to scripts when they violate the
rubric of the exam - too short, typed, too long, not enough questions
answered, etc.) and at the end, there should be an agreed mark and an
agreed rubric (if any) for each candidate. Then, these marks need to
be copied into a less regimented 'main markbook' (each student gets
two marks from two different examiners, but there can be as many as
30 examiners for a single paper) which only requires that the marks
be entered in Paper1_First_Mark, Paper1_Second_Mark, and Paper1
_Agreed_Mark (and rubric for all three) fields. I guess that's sort
of a secondary problem, compared to what I actually need help with,
though...

Ideally, what I would like to do is automate the process of creating
the mark sheets for each paper. The initial queries to get the right
people for each paper are simple enough, but I'm having trouble
figuring out how I can use the queries to get the format I need for
the markbook papers. A crosstab query *nearly* does it, but it won't
let me add extra fields (or better yet, variables for each extra
field that will use the same source as the examiner name!) and
requires some sort of mathematical function (count and sum are the
ones that come directly to mind). All the searching I did on PIVOT
(used in the crosstab) hasn't been very helpful in that you appear to
have to use it within a crosstab and there doesn't seem to be an
equivalent for other query types...

So... For those who, like me, prefer a visual example:

Student Query for Paper 1 Examiner Query for Paper 1
Candidate ID Examiner Name
1111A Jones
2222B Smith
3333C Green
4444D
5555E
6666F


Finished Table Layout with fakedata (tm):
ID Jones Jones_R Smith Smith_R Green Green_R Agreed Agreed_R
1111A 60 S 72 67
----------------------------------------------------------------
2222B 70 72 71
----------------------------------------------------------------
3333C 68 68 68
----------------------------------------------------------------
4444D 65 66 66
----------------------------------------------------------------
5555E 48 S 50 S 49 S
----------------------------------------------------------------
6666F 72 69 70
----------------------------------------------------------------

So... Any way that you all know of to do this? If I knew any VBA I'd
try to do it that way, but I'm a relative beginner to databases and
couldn't program my way out of a wet paper bag...

I'll be away for the weekend (and in fact, am just about to go home
ill - cold or something. Yergh), so if I don't get back to anyone
who might answer straight away, I will when I get back.

Thank you all in advance for any help or advice you might be able to
give me!

 

[Arvin Meyer]

You are getting ready to make a classic beginner's mistake which many of us
call "committing spreadsheet". A database is definitely not a spreadsheet.
Your structure needs to change to a number of related tables with a
structure similar to (simplified):

tblStudents - Student details and a primary key field

tblExams - Exam details and a primary key

tblExaminers - Primary Key, Details about the exam graders

tblExamQuestions - Primary Key, Foreign Key from tblExams, Question

tblExamGrades - Primary Key, StudentID, ExaminerID, ExamQuestionID, Grade

Now, you can have as many questions in as many exams, graded by as many
examiners, taken be as many students, as you like. This, of course is
over-simplified, and may be missing something, but you should be able to get
an idea. It would be a good idea if you read up a little on relational
database design before attempting to go much farther. A few good sources
are:

http://support.microsoft.com/default.aspx?scid=kb;en-us;100139
http://support.microsoft.com/default.aspx?scid=kb;en-us;289533
http://support.microsoft.com/default.aspx?scid=kb;en-us;283698

There are also several good books on designing rational databases. Designing
Relational Databases by Rebecca Riordan, and Database Design for Mere
Mortals by Michael J. Hernandez, are 2 of my favorites for beginners.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J. Steele]

Reconsider your table layout. What you're proposing is not normalized, so it
can lead to difficulties later. Rather than having multiple marks in a
single row (with the field names being data themselves), continue with each
mark being a separate row.

 

[Jen Pollard]

You are getting ready to make a classic beginner's mistake which many of us

call "committing spreadsheet". A database is definitely not a spreadsheet.

Yes, I do realize that, actually. I'd considered redoing the old
mark book in Excel (and may still if I can't find a way to make
Access do what I need it to do), but Access makes reporting, sorting,
compiling, etc. much easier. I'm willing to work harder on the
design and math (you don't even want to know how exams are marked and
degrees are classed - that's a whole other headache) in order to make
the lives of the secretaries easier during exam time.

Your structure needs to change to a number of related tables with a
structure similar to (simplified):

I'd considered this, actually - even further simplified because we
need have no data in the mark book regarding the actual content of
the exams. The only problem, and I've been met with opposition to
this idea at every turn, is that it involves the secretaries having
to enter not just the examiner's mark, but the examiner's name, in
one form or another. Since all exam marks have to be entered in a
single day (don't ask... Cambridge is weird.), that will double their
workload. We have difficulty enough as it is getting all the marks
in in a single day without adding to the work. That's why I
thought that writing some sort of code or query that created a table
with the data already formatted as it needs to be would be ideal.

Anyway - I thank both you and Doug for your efforts. I really
appreciate it. I'll just have to see what I can do with this
thing. Since the data changes so much every year (examiner names and
papers they mark as well as student information), I might just have
to do some manual editing for each year's mark book. I was pretty
much resigned to that anyway as far as reports go, so I guess a
little extra effort at the beginning of exam time won't hurt.