Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access: update data

Access: update data

Access: update data

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

RE: Access: update data

I would try a crosstab query.

Are you really storing module, assignment, and supp in a single field?
Are the values always like Mod#Ass#Supp#?
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.

CODE --> datasheet

Student#   AssignmentName   Mark
SOU0001    Mod1Ass1         20
SOU0001    Mod1Ass1Supp1    80
SOU0002    Mod1Ass1         60 

Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close