Need some ideas on building an exam result database. Assistance appreciated.

[SaviourV]

Sorry if this has been posted before, but I'm in a bit of a dilemma of sorts. Also, if this sounds rather unclear, let me know, and I'll explain things further with a few photos.

Before I begin, I came across this thread, mentioned at http://www.daniweb.com/forums/thread105907.html
...this had something to do with building up a database to store exam questions and their results.

As a novice that's a bit rusty, my needs are a little more trickier.
The short of it is that I need to build a database that can be used to create examination result transcripts.
The longer part? What I've got to work with.

As of right now, the sample examination result data that I have obtained is in Excel, and only applies to multiple-choice objective questions (not essay ones, as of yet). The first important rows indicate the number of questions, while the first important columns indicate the number of trainees that took the exam. The core of the data contains the trainees' responses to the questions in that exam, and, several rows later, the final outcome of the trainees' efforts (Pass / Fail).

Unfortunately, the columns in the file simply contain numbers, not trainee IDs, so it's difficult to identify which trainee obtained what result (pass/fail), but that's not really the main issue.

The main issue's the fact that I need to transfer the final result to the Access database I'm making. There's 248 trainees in this training facility, broken down by intake, and by the category that they're from. Currently, there are 3 categories, each containing 12-13 modules. These are known as Cat A, Cat B1, and Cat B2. Additionally, there are currently 10 intakes; 6 of these fall under Cat A, while the remaining ones fall in Cat B1 and Cat B2 evenly.

Most of the modules in each of these categories are the same, with the exception of a few category-specific ones. But to manually enter the result data into the Access database may not only cost time, but may also introduce the possibility of getting the results wrong. Think about it: 248 trainees X 12-13 modules = about 3,000 entries to key in.

Plus, there's the question on how I can make a table that relates to the exam results per module, and per category.

The good news is that this database is staying offline for now, but I don't know how far the management wants to take this.

If this is still unclear, I'll be glad to include a photo of how things look like at this stage. I'm just thinking if there's a much cleaner and faster path than manual data entry. And if programming in VBA's the only option, then tell me what I need to do to get things set up right. Also, on a related note, move this post to the right location if I've posted it in the wrong place.

Thanks.

[trudge]

Re-entering data is the *last* thing you want to do. In your case, it sounds like you have all the data you need in an Excel sheet, but need to get it into a database of some kind. You may need to get a programmer to parse through your exported Excel files and populate the database fields. This can be very gnarly and time-consuming work.

[SaviourV]

Except that by my position in the organization, I AM the programmer (and several other things rolled into one). And my boss insists on cutting corners even though I know that doing that would set up things for a collosal mess.

Still need to figure out something to get things to work right. So....data entry's the last resort, as I guessed. But the question now remains as to how to program things right.

Do you have any ideas for a schema I can use as a guide? I've been racking my brain on this for over 2 weeks now, and I need some fresh ideas.

Thanks again.

[trudge]

Have you ever designed a relational database before? You have all the data as far as I can see in order to create the proper tables and make the relationships.

[SaviourV]

Have you ever designed a relational database before? You have all the data as far as I can see in order to create the proper tables and make the relationships.

As I said before, I have gotten quite rusty, given that my past jobs had nothing to do with programming. Even now, my boss finds it hard to accept me for just that detail, which explains the extra roles, but that's a story for another time.

Anyway, the following attached files should give you an idea of what I've managed to do. There are some grey areas, though.

The first is the Intakes table, which is meant to contain the list of trainee IDs, plus the current course they're in, and the date they first enrolled in that particular course.

The second is the Transcript Temp table, which is meant to contain the trainees' results per module and per course, but the question is whether it's possible to mix intakes in, or make separate tables for each intake. The idea was initially to have a centralized data store to create exam transcripts from, really....but maybe there's something I'm missing.

Also, I've attached the sample examination result file, in Excel format. If you've any questions about that file, feel free to ask me. Maybe you might have a better way to structure that data, or something.

In any case, I really appreciate the help that you're giving me, comrade trudge, and anyone else who's reading this. If this whole thing gets solved, I'll do my best to put up a tutorial so that no one else will have to go through the same problems I'm facing here.

Thanks!

[trudge]

I don't use MS Office so those files aren't any good to me. For your database structure, can you not export that to a text file of some sort? And the spreadsheet can be exported to a .csv file.

[SaviourV]

I don't use MS Office so those files aren't any good to me. For your database structure, can you not export that to a text file of some sort? And the spreadsheet can be exported to a .csv file.

Uhm, the first three files are GIFs, actually. But I'll look into the conversion. Currently holding up the fort in a few places. I'll get back on this soon.

And are you sure you want the Excel file converted to CSV? It's pretty huge.

[trudge]

The images were not much help I'm afraid. And the .xls file in the zip attachment is no good to me either. And no, I guess I don't want the .csv file.

You did mention that you are a programmer, so you will have to get your Excel data saved to some format that you can parse to pull out the fields and then populate the database. Assign ID numbers to the usual suspects and use that to tie things together. I'm not sure where you are stuck.

[SaviourV]

Turns out that there WAS a leftover item I didn't spot the first time, alright. I've just made another table to separate the trainees into their correct intakes.

I think I'll hold off asking until I'm really stumped. I hope I manage to iron out half the issues with the Access before I start figuring out the Excel.

But thanks for your help. Even though it might take a bit more effort on my part, your assistance kind of helped me see things a little bit clearly. When I have more pieces of the puzzle, I should have a better question to ask.

Until then, keep this open.