Solved

Unequal multi-table update statement (Access)

Posted on 2008-10-03
4
617 Views
Last Modified: 2011-10-19
My wife needs a database for her work in a school. It needs to track student reading scores and compare them to a target.

The way the target goal is figured out is to look at the first score for a student in a quarter and then add 2.5 (correct words per minute) per school week until the end of the quarter.

I made a table, tblSchoolWeeks, that has each week of the school year in it along with the increase amount based on where it falls in the quarter. That way some weeks, like during winter or spring breaks, dont count towards increasing the reading level.

So what I need help with, well that would be everything, what I most need help with is with an update query. A reading score and test date is entered into tblTestScores. What I want the update query to do is grab the first test score for that student in that quarter and then add the appropriate target increase number based on when the test took place (if it took place in the 3rd week of a quarter then the target is the first score for that quarter plus 5 points).

If you could help with the update query or if you have a better way of doing this I would appreciate it.

Attached is a picture of the current table layout.

Thanks.

TableExamples.JPG
0
Comment
Question by:zabu99
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 22636337
create view studentstartscoredate as select studentid, min(testdate) mintestdate from testscores

create view studentstartscore as select ts.studentid, ts.score
from testscores ts, studentstartscoredate tsd
where ts.studentid = tsd.studentid
and ts.testdate=tsd.testdate

select sw.startdate, sw.enddate, s.studentname, ss.score*sw.targetincrease/100.0 targetscore
from schoolweeks sw, students s, studentstartscore ss
where s.studentid= ss.studentid
order by ....

I have not tested this but you may get the idea from it.  You are going to get one row for each student for each week. So 10 students, 10 weeks, 100 records.

be careful - if ss.score is an integer, the result will be an integer.  
 
You can use temp tables intead of views... it may be easier
0
 
LVL 10

Accepted Solution

by:
AaronAbend earned 500 total points
ID: 22636347
Error!!!
create view studentstartscoredate as select studentid, min(testdate) mintestdate
from testscores
GROUP BY STUDENTID

sorry about that...
0
 
LVL 5

Author Comment

by:zabu99
ID: 22637135
That's going to take me a couple of minutes to wrap my brain around, but I'll get right on it. Thanks.
0
 
LVL 5

Author Closing Comment

by:zabu99
ID: 31502838
Sorry to leave you hanging so long, I took on a bit of a cold on accident.

Yeah, if I modify this a little bit it should do the trick. Thanks for the (huge) push in the right direction.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now