How to I create a SUM function in Microsoft Access?

Question

The database I work with is too large for Excel. It truncates about 20,000 records.

Answer 1

'In module1:

Option Compare Database

Sub main()
x = mySUM
MsgBox x
End Sub

Public Function mySUM()
mySUM = DSum("[amount]", "Table1")
End Function

'Where you can use the function mySUM inside form.
'OR if you prefer Query then put this on Query1:

SELECT sum(AMOUNT) as mySUM
FROM Table1;

Answer 2

Okay, what I did was assumed that each class was worth 3 units. Because in order to truly determine GPA you must define a unit value for each class... I also assumed that your Classes, and Letter Grades fields in your Classes Taken table were comma separated without spaces.. I wrote the following function to determine if a student is an Honor Student: Public Function IsHonorStudent(Student As Long) As Boolean   ' Declare some variables   Dim cnCurrent As ADODB.Connection   Dim rsClassesTaken, rsGradeValue As ADODB.Recordset   Dim strSQL, strGrades() As String   Dim i, GradePoints, TotalPoints As Long   Dim Units, GPA As Double   ' Set the connection and recordset   Set cnCurrent = CurrentProject.Connection   Set rsClassesTaken = New ADODB.Recordset   ' Build your query and run it   strSQL = "SELECT Grades FROM ClassesTaken WHERE StudentID = " & Student   rsClassesTaken.Open strSQL, cnCurrent   ' Make sure we have a record to play with   If Not rsClassesTaken.EOF And Not rsClassesTaken.BOF Then     ' Split the comma separated grades string into separate values.     strGrades = (Split(rsClassesTaken!Grades, ","))     ' Initiate the TotalPoints     TotalPoints = 0     ' Loop through the Grades to determine points for each.     For i = 0 To UBound(strGrades)       Set rsGradeValue = New ADODB.Recordset       strSQL = "SELECT PointValue FROM GradeValue WHERE Grade = '" & strGrades(i) & "'"       rsGradeValue.Open strSQL, cnCurrent       GradePoints = rsGradeValue!PointValue * 3       TotalPoints = TotalPoints + GradePoints       Set rsGradeValue = Nothing     Next     ' Now we can determine GPA     Units = 3 * (UBound(strGrades) + 1)     GPA = TotalPoints / Units     ' Return whether the student is an honor student     If GPA > 3.5 Then       IsHonorStudent = True     Else       IsHonorStudent = False     End If   Else     IsHonorStudent = False   End If   ' Reset connection and recordset   Set rsClassesTaken = Nothing   Set cnCurrent = Nothing End Function Then call the function in this way: IsHonorStudent(1) This will return a true/false value to tell you whether the student with a StudentID of 1 is an Honor Student based on whether their GPA is above or below 3.5. Let me know if you have any questions! I know it may be a bit confusing for a beginner...

Answer 3

DOn't want to sound like a wuss, but wouldn't you prefer Excel? I'm a ACCESSOPHOBE!!!