best way of setting up exam database

Hi,

I am trying to set up a database that takes students exam results for different modules, selects the highest module marks and totals them to work out their overall score, also determining how much they need to achieve certain grades.

I have a spreadsheet that is exported from the exam board as a starting point which contains the following fields in the following order (have tried to add a dummy attachment but it isn't working..):

Series Year Series Code Candidate Number Candidate Name Candidate Status Specification Code Entry Code Entry Title (and Suffix) Result Grade Result UMS/Points Result Mark


I need to show the explained data for each Candidate Number using the maximum Result UMS/Points for each Entry Code (candidates have done resits so there are duplicate entry codes).


Anyone any helpful suggestions as the best way to do this?..

I was going to do it in excel but am not great with macros if they are required.. About four years ago I got quite good with access but I have forgotten how to use it although could possibly work it out if someone gave me a format that worked...

My main problem (having sat here for 3 hours on both excel and access) is that I can't figure out how to select the maximum UMS for EACH of the entry codes (6 exams in total) for EACH candidate.


Many thanks in advance

N

ok I think I have figured out (finally after a zillion hours!) that a pivot table does what I want... except for the extraction of the highest mark achieved for each module.

I can see how to add a column for the highest marks outside the pivot table using = MAX(cell references) but this does not grow with the pivot table if new data is added.. which it will be.

I also want to add a grand total of the highest marks for all modules for each student but am again not sure this is possible in a growing changing pivot table?

the table I have created at work is actually in excel but I only have open office at home so the attached has been done in that as an example. My actual table also includes the totals for each Series Code.

any ideas?

cheers

N


exam data.xls

exam data.xls

sorry that file seems to remove the data pilot (pivot table) and I don't know how to get rid of the totals column at the end, I don't need that.. the max formula goes on the end column.

N