+ Post New Thread
Results 1 to 8 of 8
Office Software Thread, Import Exam Results to Access in Technical; How would you import exam results held in Excel into an Access database? Currently I have them stored in the ...
  1. #1
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Reigate Area, Surrey
    Posts
    4,428
    Thank Post
    884
    Thanked 416 Times in 316 Posts
    Blog Entries
    61
    Rep Power
    328

    Import Exam Results to Access

    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

  2. #2


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    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)?

  3. #3
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Reigate Area, Surrey
    Posts
    4,428
    Thank Post
    884
    Thanked 416 Times in 316 Posts
    Blog Entries
    61
    Rep Power
    328
    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.

  4. #4


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    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?

  5. #5
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Reigate Area, Surrey
    Posts
    4,428
    Thank Post
    884
    Thanked 416 Times in 316 Posts
    Blog Entries
    61
    Rep Power
    328
    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.

  6. #6


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    Quote Originally Posted by CAM View Post
    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.
    Last edited by pcstru; 30th August 2011 at 03:51 PM.

  7. Thanks to pcstru from:

    CAM (30th August 2011)

  8. #7
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Reigate Area, Surrey
    Posts
    4,428
    Thank Post
    884
    Thanked 416 Times in 316 Posts
    Blog Entries
    61
    Rep Power
    328
    Do you have an example sheet you could share please? That sounds like quite a complicated but interesting setup

  9. #8


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    Quote Originally Posted by CAM View Post
    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 ....



SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] Exam results import problem
    By Stuart_C in forum MIS Systems
    Replies: 17
    Last Post: 26th August 2010, 09:08 PM
  2. Replies: 4
    Last Post: 31st August 2007, 12:31 PM
  3. What are we and aren't we allowed to access??
    By maniac in forum How do you do....it?
    Replies: 11
    Last Post: 9th March 2007, 10:58 AM
  4. pupils able to access c drive via word 2000 web toolbar
    By projector1 in forum Office Software
    Replies: 22
    Last Post: 8th December 2005, 09:44 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •