Looping to Calculate Running GPA

[tonere]

Good Afternoon,

Thank you all for looking at this in advance!

I am trying to put code into a module that will return a table with running GPA hours. I have read the MS Do...Loop help and searched but I do not understand.

I have a table that contains GPA data by term for which I can calculate that particular term's GPA. But what I need to do is ba able to look at a cumulative GPA, by Level at any term.

My data table looks like this, of course there may be dozens of terms for each Level:

ID		Term		Level		HOURS_ATTEMPTED		HOURS_EARNED		GPA_HOURS	QUALITY_POINTS
4		199020		U		12		12		12	45
4		199050		U		6		6		6	18
4		199070		G		6		6		6	19.5
4		199120		G		3		3		3	12

I would like the output to look like this:

ID		Term		Level		HOURS_ATTEMPTED		HOURS_EARNED		GPA_HOURS	QUALITY_POINTS
4		199020		U		12		12		12	45
4		199050		U		18		18		18	63
4		199070		G		6		6		6	19.5
4		199120		G		9		9		9	31.5

Thank you all again!

[June7]

Have you tried building a report using Grouping & Sorting with aggregate calcs functionality? Textboxes in report have RunningSum property.

[tonere]

Hello June7, and thank you for your reply. I created a report, grouped on "ID" and "Level", sorted ascending on "Term" and made the running sum by group and it returned the information I needed in report form. Awesome.

I need this in query or table form so that I can link an everchanging population of students (table) to this result to calculate and return their cumulative GPA for any term.

Any thoughts?

Thank you again!

[orange]

Why can't you use the query in the format shown in post#1 as the recordsource for a Report that manipulates the data the way you want?

You get a Report instead of a query, but the process is/could be automated --at least that's how I see it.

[June7]

tonere, I don't understand your last post. The report is linked to the source data, otherwise there would be no results, and will dynamically reflect the changing data.

[tonere]

Hello Orange. Thank you for your reply. The first table in post#1 is my data table, the second table #2 is what I would like a query to return, so I could link it to other tables and query off of it. My issue is that I am routinely given a list of hundreds of students, say table #3 (not shown), and what is requested in return is their cumulative GPA at a certain term in their academic career, based on their level. The datasource gives the hours and quality points for each individual term, but to return a cumulative GPA, you must sum the Quality Points and divide by the sum of the GPA hours.

So in the example of my table #1, student 4, for level=U, first term 199020, their GPA was (45/12). Student 4, for level=U, second term 199050, their GPA was (18/6), but their cumulative GPA was (63/18) which is ((45+18)/(12+6)).

The list of students I am given (say table #3) contains ID, Term and Level and may contain the same student (different term and/or different level), or different students (different terms, different levels), etc. So I would like to run a query linking table #3 to table (or query) #2 by joining them by ID and Term and Level to get the cumulative GPA.

[orange]

It's only data that is manipulated and reported. The processes are consistent. Only the data values change. I don't see your issue.

If your concern data relates to different time periods, well you have to process all the data for the student(s) involved and report it.

Still don't see the issue.

[tonere]

Hello June7. The report worked perfectly as you suggested, but what I need is to be able to query off the information. See my response to Orange above. Thank you all!

[tonere]

I need to return the data in an Access table, not a report.

[June7]

Make the report's RecordSource a query, not just the table.

If you want the running sum in query, review this http://support.microsoft.com/kb/205183.

But why? Report is so much easier.

[tonere]

Hey June7. I created the module and it ran fine, the problem is that it will return a running sum for each student (ID), but it does not distinguish between levels. When the student's level changes (like fron U to G) it keeps totaling and does not begin to start to calculate again from the new level. I tried to add a second group to the function [Function fncRunSum(lngCatID As Long, lngCatID2 as Long, lngUnits As Long) As Long] but do not know how to incorporate this new level into the rest of the module.

Is there a way to change the function to make it look at ID and Level, not just level?

You are right, the report is much easier! But I return the data in an Access table so that further analysis can be done by others. I believe they take my data (and data from other offices) and use it for regression analysis.

Function fncRunSum(lngCatID As Long, lngCatID2 As Long, lngUnits As Long) As Long
    'Variables that retain their values.
    Static lngID As Long
    Static lngID2 As Long
    Static lngAmt As Long
    If lngID <> lngCatID Then
        'If the current ID does not match the last ID, then (re)initialize.
            lngID = lngCatID
          
    Else
    
    If lngID2 <> lngCatID2 Then
        'If the current Level does not match the last Level, then (re)initialize.
            lngID2 = lngCatID2
            lngAmt = lngUnits
    Else
            'If the current ID matches the last, keep a running sum for the ID.
            lngAmt = lngAmt + lngUnits
    End If
    End If
    'Pass the running sum back to the query.
    fncRunSum = lngAmt
    
End Function

[June7]

I tested that original code, funny animal. The results in the query are correct when the query first opens but if I do anything else the query keeps compounding the sum. I have to Compact & Repair the project to get the sums back to correct values. Beginning to regret I found that code and referred you to it, especially without trying it.

The only other solution I could suggest is VBA code that calcs the running sums and saves the results to a table, not a function called by query. The code would loop through a recordset of the source data, sum and save a record to a 'temp' table. The sum variable would be reinitialized to 0 when the ID or level changed. This table would be purged each time the procedure is run.

[tonere]

Thank you June7 for all you time and wisdom. I will research VBA code that calculates a running sum and saves the result to a table as suggested.

With thanks, tonere...