Using data in one table to create fields in a new table

[jenp]

Hi there

I'm not sure if I've picked the right forum topic for this... It's not a straightforward table question, and I think the only way I can do what I need is with a query of some sort. Apologies if it should have gone in 'Tables'...

The situation is: I'm creating a database (using Access 2003) in which our secretaries can enter exam marks for students. The difficulty is that there are ten papers (exams), each student's exam is marked by at least two examiners and there are at least 6 examiners per paper. Every examiner also marks more than one paper, but doesn't mark all papers.

So, I've got a table with all of the examiners and the papers they're marking and I've got a table with all the students and the papers they're taking. Because we have to enter the marks in such a way that we know both which mark an examiner gave and (obviously) which mark a student received (e.g. so we can look at statistics for each examiner), I thought the best way to do this would be to run a query that created a new table per paper in which the field names would be: Student_ID, <name of examiner 1 for the paper>, <name of examiner 2 for the paper>, <name of examiner 3 for the paper>, etc until the end of the list of examiners for that paper. The student's number would populate the Student_ID field and then the secretaries would enter each examiner's mark in their named field. Like this:

| Student_ID | A.N. Other | J. Blogs | J.Q. Public | Agreed_Mark |
     1234A         72                       74            73
     2345B                     65           68            67
     3456C         71                       73            72
     4567D         52          51                         52

Listing the name of every examiner (including those who aren't marking that particular paper) isn't an option (and would have to be hard coded into a table in any case, as far as I'm aware, either in a query or in a table design. This is something I'm loathe to do). Ideally, the secretary would be able to press a button on a form and have all the mark entry tables for all the papers created automagically.

The examiners for the different papers change every year, and not every academic marks exams every year, so this data will not remain static. Hard coding a table with examiner names would make more work for the secretaries. (I want to avoid this as all mark entering for all students is done in a single day. This means over 600 students at 3 marks per student [mark 1, mark 2, and final agreed mark] with time to contact examiners with questions... and only 2 secretaries, each of whom deals with a different section of students).

Because of the nature of the marking, it would also make quite a lot of work for the secretaries if we had to cross reference a generic Examiner_1 field with a different examiner for each paper, so the field name really must be the examiner's actual name.

Can anyone help me? I'm happy to do it with either VB or SQL; I'm not a programmer, but I can usually work out what code does even if I can't write it myself. I should be able to adapt anything presented as long as it's complete and doesn't assume that I know the basics. I've also got two reference books to hand: _Microsoft Access 2003 Forms, Reports and Queries_ by Paul McFedries and _How to Do Everything with Microsoft Office Access 2003_ by Virginia Anderson.

Thanks very much for your time.

-Jen

 

[Dreamweaver]

I think You'll need to use a crosstab query for that but that will only gives you a set of results so don't really help with the title statement LOL.

Mick

 

[jenp]

*smile* Thanks for the input! I've already had a look at a crosstab queries (and Pivot Table forms), and they don't really do what I want. In a shallow, first glance kind of way - yes, they have the examiner names as the column headers, but yeah - they're not going to be able to do what I need them to. And, I can't figure out how to get the appropriate crosstab stuff into a make table query without errors. Which makes sense, I think, because they're trying to accomplish two different things.

I guess I was hoping for some way to dynamically assign field names from data returned in a query into a 'new table' query... or make the table with VB and query results... or something. I think I'm just going to have to rethink the design of the tables and use some sort of form/subform. It'll mean a bit of data duplication, but I don't know if there's a way around that.

Thanks for your help, anyway; I appreciate it!

-Jen

 

[boblarson]

Your database design is not normalized and therein lies the problem. If you normalize your database you can get the data out the way you want it with ease.

Do a search here for normalization and I'll also try to give you the 20 second explanation (as I can't write a lot right now).

You don't want to have tables where you need to add a table per paper, nor do you want to have a column for each examiner's results.

This is a simplified sample of what you would want:

tblStudents - table for students
StudentID - autonumber and primary key for students table
StudentFirstName
StudentLastName
StudentNumber (if you want a specific number associated with a student)
StudentAddress - optional if you are tracking that info
StudentCity - optional, same as above
StudentRegion - optional, same as above
StudentPostalCode - optional, same as above
StudentPhone - optional, same as above

tblExaminers - Examiners table
ExaminerID - Autonumber and primary key
ExaminerFirstName
ExaminerLastName

tblExams - table for exams
ExamID - autonumber and primary key
ExamDescription
ExamDate

tblExamResults
ExamResultsID - autonumber and primary key
ExamID - long integer and foreign key (primary key from Exam Table)
ExaminerID - long integer and foreign key (primary key from Examiner table)
StudentID - long integer and foreign key (primary key from Student table)
ExamResults - value of the exam

With each of these like this, you can add exams, examiners, students and results of exams with no problem using queries, forms and subforms.

Your gathering of results will be much easier as you can do a query to pull the overall results, query by examiner, query by students for their overall results over many exams, etc.

 

[jenp]

Thanks for taking the time to do all that, Bob.

I do actually know about normalization, but in this case I'm having to forgo doing it properly in order to make life easier for the people who have to use the database. I've had this discussion before with people about this particular database, and that's why I've been putting it off year after year. However, it's getting to the point now where our current exam marking system is too old, clunky and out-dated to be any good to us.

The secretaries have to have a way to quickly import the data provided to them by the examinations board directly into a table and then work from there. We haven't got time to faff about with entering each student's exam data (which is student_id, followed by, in Part I, 10 fields corresponding to an exam number and an indication about whether that student is taking that exam) manually.

We don't even really have time, during the exam week, to faff about with reformatting the data provided by the BoE - especially as all we'd be doing is separating the student_id from the exams they're sitting, which we'd then have to link back together somehow (and seeing as all student_ids are unique, that bit of data seemed the best primary key - why separate the student_id and then put it back in to another table?). That's why I'm trying to automate as much as possible based on the information we get from the Board.

The only info we need about examiners is their surname and an indication of what exams they're marking.

The only information we need about the exams is the paper number.

As I said, not all students take all exams - in fact, the most that any one student will take is 7 out of the 10 (or, in part II - 6 out of about 20). Each student's exam is marked by two out of at least six (but upwards of 20 in some cases) examiners for that paper. We absolutely MUST have some way of indicating which examiner gave a particular mark to a student's script for a particular paper without interrupting the mark entry process and without having to go back afterward to find the paper mark entry form on which one of the examiners wrote the student's mark.

We must also have displayed, at the time of data entry, the other examiner's mark (remember, each student's script has two.. and student 1234A may not have the same two examiners as student 2345B and the examiners for student 1234A for paper 1 may not be the same as that student's examiners for paper 5) so that we can compare the two and check that the mark that the examiners agreed upon is the same in both cases. Having a column for each examiner seemed the most sensible solution to this problem.

However, despite the fact that I'm arguing 'against' normalization in this instance, I would be more than glad to read any ideas you may have!

What I haven't yet explained (I didn't see the point, but I was probably wrong ) is that there are various mathematical comparisons that have to be made in order to determine a student's overall class for the Tripos. The agreed marks for each individual exam don't make up the whole of the 'results' for each student. What determines a student's final classing is based on several factors:

1) whether a certain number of the interim marks from each individual examiner for that student's scripts meet certain criteria
2) Whether a certain number of the agreed marks from each set of examiners meet certain criteria
3) whether or not the aggregate of the agreed marks from each set of examiners for that student is above a certain number
4) whether the average is above a certain level, depending on the number of exams the student sat

This isn't a simple 'If they get an 80 it's a First' sort of exam markbook, as you can see... The data that's required for the statistics lends itself well to Access, I think... It's so much easier to find exactly what you're looking for. I have seriously considered doing the bulk of the mark entering in Excel and then using Access strictly for reports, but I would have to construct something nearly as complicated as what we're currently using, and I'm not up to doing it Excel in any way (as opposed to Access, where I'm slightly more fluent).

I *can* make Access do what I need it to in a very untidy way involving at least two complicated tables, two uncomplicated tables and duplication of some data. But I'd rather not have to... I'd rather be able to take the data we get from the BoE and create what I need for a more normalized database from that.

Anyway - sorry this is so long (again!) but, I hope what I'm trying to do is a little more clearly explained.

Thanks again for your time - I do appreciate the effort and the care you took in trying to help!

 

[Dreamweaver]

Just A Quick Question how to you get the data from the examinations board and could it be imported?

 

[jenp]

What I'm asking for is not design advice, but some way to create or alter a query that will make a table with dynamically assigned field names based on data I already have. I know my design, as presented here, isn't ideal, but normalization is a way to omit needlessly repeated data, and I believe I have done that to the best of my ability whilst keeping to the constraints within which I must work.

On further pondering, Bob's response has given me a few ideas about how to use the BoE student data and the examiner data to create tables which will suit, but I do still believe I will need to create some tables with dynamic field names, as described. I'm sure it's something Access ought to be able to do, as I've seen it done in other SQL-based applications. I just don't know - and can't find any information about! - how to go about it in Access. Which is why I asked.

Dreamweaver: The data from the board is sent in a comma delimited format with the student_id and the paper numbers of the exams they'll be sitting. Yes, it can be imported. I've actually done that. So, I don't actually have a database yet, as such, just the two tables with students and which exam they're sitting and examiners and which exams they're marking. The 'working' database will draw information from those two tables (the data in which will change every year).

 

[boblarson]

I know that you're probably getting frustrated with us, and you say you know about normalization, but it appears from your answer that you either don't know about the implications of what you said, or you don't care about them.

Either way, I am going to attempt to explain in further detail why, even though you've made up your mind to do this regardless of what we say, you should rethink what you are doing.

I really don't know how much Access experience you have, but between Pat and I we have over 20 years of experience (at least). I don't know for sure exactly how much Pat has, but I have been doing this for over 10 years now and Pat was on this forum before I joined it back in 1997 (even though it says 2001 on my profile because they changed forum bulletin board software back then and it didn't transfer the joined date). So, we've been there and done that and we are talking from practical experience.

Here are some of the reasons why I seriously think you should rethink your plan:

1. Normalization is not just about keeping data non-duplicated. In a relational database the way that you set up your tables, and their relationships, have a big determination as to what you can get out and with what ease you can do it.

2. Creating a new table for each exam is going to make your life (or someone else's) a living hell, if not now sometime in the future. Try to think about the purpose of the database. You want to provide information to someone and as the developer you want to make it as easy to use for your user as possible (you mentioned that you wanted to make data entry easy for your users). Now, if you create a table for each exam, how does that make it easier? Easier for you in development, but if a user needs queries to get the data out, then they have to create a BRAND NEW QUERY, or set of queries for EACH TABLE or they will have to go in and modify the tables that are in each query and change the field references to reference the correct table and then remove the old table in the query so the data shows correctly, remembering to add the new table, make the changes in references to the fields, and then remove the old table, otherwise all of the fields disappear and then they have to bring all of the fields back down into the query QBE anyway. If there are any calculated fields in any of the queries, then they have to make sure to change the field references to the correct tables as well. (and this is easy for the user, how?)

3. If you want to get any aggregate data from any of the tables, the column structure makes it EXTREMELY hard to do so as the data is held in different columns, when in a normalized database it is all in the same column and therefore EASY to create aggregate calculations.

4. To be user friendly, you should have forms and reports that are quick, and easy, for the user to run. They should not have to be an Access developer to run queries and reports (if they want to use a report, you would have to change the underlying query, like mentioned above, to just view the report and that is not the way they should do it). If you have forms to select date ranges, score ranges, etc. and then they can open a report based on the parameters in that form it is easy for them and they don't start to hate how clunky and painful you've made it for them.

5. See your quote:

We don't even really have time, during the exam week, to faff about with reformatting the data provided by the BoE - especially as all we'd be doing is separating the student_id from the exams they're sitting, which we'd then have to link back together somehow (and seeing as all student_ids are unique, that bit of data seemed the best primary key - why separate the student_id and then put it back in to another table?). That's why I'm trying to automate as much as possible based on the information we get from the Board.

By doing what you're doing you are NOT AUTOMATING anything. You are trying to avoid automating as true automation is creating a process, regardless of how things currently exist, to make the process happen automatically (the WHOLE process) and easy for the end user.

The secretaries have to have a way to quickly import the data provided to them by the examinations board

Yes, agreed and that is why you create the process to import the data and that can include automating the Data Transformation which brings it in the way that the data is currently and transforming (just like SQL DTS packages) into a form that is usable for your users.

Now, I know that I probably haven't swayed you but I want you to know that I have been in your position and I took over 3 large reporting databases that were built for reporting data to Group Health Cooperative's CEO and upper level management staff. The person who designed this (and then moved to a different job) did it EXACTLY the way you have described. In fact, because they had to change the queries (to run reports for EACH physician - over 200 providers) for each report that they had to run it took them 2 weeks each month to run the process. After I redesigned the process for the USER's ease - including automating the way data was being imported and the table design, it took only 2 HOURS for them to run the reports.

So, yes, there is a way to programmatically create a table.
It is fairly code intensive if you need to change your field names and datatypes (look into ALTER Table) and I haven't needed to do so and avoid it. To copy a table in your database, you can just use:

DoCmd.CopyObject , "NewNameHere", acTable, "SourceObjectNameHere"

Then you can use

Dim strSQL As String
strSQL = "DELETE * FROM YourNewTableNameHere"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)

to remove the data.

Good luck in all you do and in the decisions you make. I do hope you will choose wisely and avoid the dark side. May the force be with you

 

[jenp]

*smile* I guess it does sound a bit like I'm getting frustrated, but I'm not! I know you're trying to help! (My last post originally had a bit that said I didn't mean to sound contentious or ungrateful to the people who'd actually tried to help).

I do see where you're going with your latest response. I wasn't entirely sure about the queries bit at first, but I've been playing WoW all day, so my brain is mush. Thanks for explaining it (and for the example). No one else I've ever asked about this in the last 3 years has done more than tell me I'm 'thinking about it all wrong'. I knew doing it the way I mentioned here would be the most difficult way for me as I'd be the one setting it up every year (because of data protection laws, all of the data - ALL, even the examiner names - has to be wiped out every year *groan*), but I thought it would be easiest for the users, who would just have to press a button. (If this wasn't already so long, I'd tell you the hoops we have to jump through at the moment with the current markbook. Each secretary has 11-12 pages of documentation about setting up and using her own section.)

I'm now thinking that perhaps the way I actually *started* doing it a couple of years ago (for which I was also told I was 'doing it wrong' by people who are supposed to know more about these things) may be a much better way. Just to check what you think, though, if you have time (Hmm... should probably be moved to the database design thread, but I reckon we're nearly done):

I'd started out with a table that had a record for each student, still using their student_id as the primary key. I'd populated that table in the same bit of code that imported the data from the BoE. This table contains fields for all of their marks for all of their papers. This would have been loaded in a form that ran a macro that ran a query (connected to the ankle bone! ... Sorry, bad joke. {shame}) on whatever paper number was entered by the secretary when she opened the form. That way, the secretary would only get the fields she's interested in. The secretary can select an examiner from a drop down linked to the list of examiners to enter their run of marks for that paper.

This main form then also writes each mark, student_id, examiner_id and paper number to a second table using a form data copying thing I can't remember anything about at the minute - and since I'm on my Mac, I can't even look it up, but you probably know what I mean (you know - the thing where, when you put data in one text field it copies it into another, too). So, essentially, each mark has it's own record and identifying data associated with it. The second table can then be queried for statistical analysis of individual examiner's marks in totum, and per paper, gender stats against data received from the BoE, individual college statisics, and various other iterations I can't think of right now (See? Mush brain tonight - Ooo, hey - and it's almost 11pm! Another excuse!). The maths for their class would be done in a query or in a report (I never got that far).

It'll be obvious I'm not a DBA or anything of that nature - I'm an archaeologist turned *NIX sysadmin who's now also adminning Netware and Windows boxen, web-mistressing, network adminning, doing tech-support, and various 'other duties as assigned'. Some of which have been to learn enough about Access, MySQL and ASP to fix things that are broken and set up new things as needed (including a couple of 3-day training courses... two bloody days of designing a SQL database normalized to the extreme, and the third day putting half of the fields back together in one table. Argh. So yes, I was ignoring it in this case, but I had gone so far that I hadn't considered 'next year', I was just trying to get 'this year' to work. My mother was right to call me stubborn).

But hey, if you ever need your CSS to to float a box within a box, your Apache web server secured or need a couple of Nortel network switches updated, I'm your woman.

In any case, thank you for the code! I'm sure it will be useful at some point *anyway*, even if just to take apart and use for other things (Classical archaeologist, see... very Roman, cannibalising bits of things for other uses). And thank you _very_ much for taking the time to actually help instead of just telling me I'm wrong with no other explanation.

 

[boblarson]

If you wish, you can send me what you have so far, a list of what you think that you will need out of it and then a copy of the original file that you have and I can assist you in suggesting some ways that might be efficient.

Send me a Private message and I'll give you my email address.