General Networking/Lan/Wan/access update

Advertisement


Question
query
query  
QUESTION: Dear Scott
I wonder if you could help me with a query.  I have two tables:
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

ANSWER: The best way is with a query, not a table. You should use a crosstab query to display all the marks in a row.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Ok - I have never had any luck with cross tab queries but will give it a go. Any chance you could give me some pointers? Can I create a temporary file with the results of the update query?
I appreciate your help, Kary

ANSWER: First create a query that returns the Student #, Module, Assignment and Mark for the highest mark per student. Then use that as the source of your crosstab query. Use the Crosstab query wizard to select the Student# as the RowHeading, and Assignment as the column heading, then select the Mark for the detail.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks Scott, it worked perfectly!  Ok - is it possible to transfer these results to a table using an update/make table query? I'm not sure how long the cross-tab query will take to run with 5000-20000 records but, in the past when I opened reports in the "Report" option of my program, my query (not this one - it was a union query)  recalculated each time which was very time consuming.  As a result I transferred the info to a table which was updated each time the reports section was entered.  
Regards, Kary

Answer
Yes, if performance is an issue, you can use a Make Table query with the crosstab as its source. I do this at times.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

General Networking/Lan/Wan

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

Most general questions on LANs, especially home networks. Some admin and design issue.

Experience

I've been an IT Professional for more then 16 years. I've accumulated a larege store of knowledge that allows me to answer a wide variety of IT related questions.

©2016 About.com. All rights reserved.