Access: update data

[karysmithers]

Hi
I wonder if anyone could help me with a query. I have two tables with the following fields (see example below)
AllMarks: Student#, AssignmentName, Mark
FinalMarks: Student#, Mod1Ass1, Mod1Ass2, Mod1Ass3, Mod2Ass1, etc
The marks for each assignment are stored in AllMarks - each assignment has an original mark and up to 3 supps marks if necessary:
I want to take the highest mark of these per student# per module and assignment and update the appropriate field in FinalMarks - i.e. if left(AllMarks.assignmentName, 8) = Mod1Ass1, then that field will be updated in the FinalMarks table. I would prefer not to hard code it to a particular field as there are potentially many assignments.
The bottom line is that I have 6 modules with about 5 assignments per module and up to 3 supp marks per assignment. However, for purposes of reporting, exporting, etc I need the “final” marks per assignment per student to be presented as a row in a table (or query). What is the best way to do this?
Many thanks, Kary Smithers

AllMarks: Student# AssignmentName Mark
SOU0001 Mod1Ass1 20
SOU0001 Mod1Ass1Supp1 80
SOU0002 Mod1Ass1 60 etc

FinalMarks: Student# Mod1Ass1 Mod1Ass2 Mod1Ass3 Mod2Ass1 etc
SOU0001 80
SOU002 60

 

[dhookom]

I would try a crosstab query.

Are you really storing module, assignment, and supp in a single field?
Are the values always like [tt]Mod#Ass#Supp#[/tt]?
Are there ever double digits?
How does your sample data end up generating column headings where there is no data to support them?

You should consider using the TGML buttons above the posting input box to format your posting. I used the code tag (looks like a scroll) with underlining to make your sample data more readable.

AllMarks: 
[u]Student#[/u]   [u]AssignmentName[/u]   [u]Mark[/u]
SOU0001    Mod1Ass1         20
SOU0001    Mod1Ass1Supp1    80
SOU0002    Mod1Ass1         60

Duane
Hook'D on Access
MS Access MVP