[ms office - 2007] Import Exam Results to Access

[CAM]

How would you import exam results held in Excel into an Access database? Currently I have them stored in the spreadsheet with the student name as the row header, subject as the column header and grades in the cells. I'm not sure how to a) Store this in the database and b) Import the data properly.

 

I was going to have the subject name as a database field but is it better to store the subject name in a field called subject?

 

E.g, StudentID | Subject_Name | Grade instead of StudentID | Maths_Grade | English_Grade | Science_Grade

[pcstru]

There are two basic approaches. Write a Macro which will parse the data and insert the data into the appropriate tables or use excel to manipulate the data into a form suitable for CSV type import into the database. I'd probably use a VBA Macro. Before going further though, do you not have some MIS which already holds the data (SIMS/CMIS etc)?

[CAM]

Ahh yes, we have SIMS but at the same time I have been asked to see how Access fares for analysing reports when we export from AM7. This makes for a nice dry run.

 

I know AM7 has Discover but we'd need a new SIMS server before using that.

[pcstru]

Ok, so you have SIMS and the results are effectively in the SIMS database - probably in much the same form you would want to store them in an access database. You perhaps want more flexibility in accessing the data but I'm kind of struggling to see what you really want to do with access. It's important if you don't want to waste a lot of time...

 

I have been asked to see how Access fares for analysing reports when we export from AM7

 

What exactly do you mean, "analysing 'reports"? I'd presume you want to get to some kind of flexible analysis such that you can compare targets vs assessment results and perhaps break that down by subject, tutor group possibly with some kind of breakdown by attributes such as Gender, FSM eligibility, ethnicity? Maybe for GCSE results you are interested in A-C counts and overall 5 A-C's including English and Maths? Is that close?

[CAM]

Yeah that's the kind of thing I am looking at. We do it in Excel but we have to shift formulae about to accomodate new students, new subjects and so on. Everything has to be set up in exact positions on the screen after entering data and is very time consuming, even if we set the sheets up before the results go into SIMS.

 

As for analysing them in SIMS, I only know how to export from Exams Organiser and the AM7 formulae to be a bit too restrictive.

[pcstru]

Yeah that's the kind of thing I am looking at. We do it in Excel but we have to shift formulae about to accomodate new students, new subjects and so on. Everything has to be set up in exact positions on the screen after entering data and is very time consuming, even if we set the sheets up before the results go into SIMS.

 

You can avoid that by good use of excel. We started off with template sheets where we just pasted in data which came straight from predefined reports. The basic reported data was lists of results and targets (in a similar form to yours), a list of student attributes (id, name, tutor group, gender, FSM, etc), a list of the teaching groups (studentid + subject code, teaching group code) and a list of teachers with their teaching groups. Each report got pasted into a sheet wholesale. Everything else then referenced these sheets always going beyond the maximum amount of data. Updating was then a matter of running the reports, pasting in the data and saving the workbook. We've moved on a b it and now use VBA to pull the data straight from the database and build the sheets dynamically.

 

I'd say excel has better capability as an analysis tool than any static report from a database. So you may find you are going from SIMS (or other source) into excel then into access and then, either having to write a lot of reports to pull the data back from access or pull it back into excel to take advantage of better analysis facilities. Once the data IS in access, then you don't simply need to import it, you will need to either wipe it all clean and re-import everything OR have some facility to update records you have already uploaded (and possibly delete records you have no further use for (or start storing things like start/end dates). That suggests you should consider carefully whether you actually need to go into access because if it isn't a very simple requirement it will get complicated (expensive) very quickly. And if it is a simple requirement, you are probably better off just using excel but learning how to put together spreadsheet which don't require a massive amount of work every time a simple bit of data changes.

Edited August 30, 2011 by pcstru

[CAM]

Do you have an example sheet you could share please? That sounds like quite a complicated but interesting setup

[pcstru]

Do you have an example sheet you could share please? That sounds like quite a complicated but interesting setup

 

Unfortunately I don't have the sheets with anything but real data in them. I started working on a document on how to put them together and then started working up some random test data. Unfortunately I've not had time to complete that - generating reasonable test data is quite a complex task in itself (probably more complicated than the final sheets!). I did describe them here. I will try and put something better together soon - but that promise was also made back in that thread ... where does the time go ....