user3781528 user3781528 - 1 year ago 42
SQL Question

Select a record with the largest sum of fields (Access 2010)

I would like to select a record from a table based on the field “labcode” specified by the user on a form. There could be multiple records associated with each “labcode” and I would like to select a record that has the highest sum of 10 corresponding fields in the “tblDSA". Fields are named as follows: “A1_MFI”, “A2_MFI”, “C1_MFI”, "C2_MFI", "DR1_MFI", "DR2_MFI"…)

All 10 fields are in 'text' format and sometimes contains a number, text or are left blank. I would only like to sum up records that contain a number in that field. Do I need to create a new field in “tblDSA” that holds the total score or should I avoid storing calculating values in the table?

Dim SQL As String
Dim db As DAO.Database
Dim tblDSA As DAO.Recordset

Set db = CurrentDb

Set tblDSA = db.OpenRecordset("tblDSA")

SQL = "SELECT * Nz((Val[A1_MFI])) + Nz((Val[A2_MFI])) + Nz((Val[B1_MFI])) + Nz((Val[B2_MFI])) + Nz((Val[C1_MFI])) + Nz((Val[C2_MFI])) + Nz((Val[DR1_MFI]))+ Nz((Val[DR2_MFI])) + Nz((Val[DQB1_MFI] + Nz((Val[DQB2_MFI]))as TotalScore FROM tblDSA WHERE [LABCODE] = " & Me.tbLabcode.Value & " ORDER BY TotalScore DESC "

Debug.Print SQL

Set rs = db.OpenRecordset(SQL)

The SQL above contains a syntax error (missing operator), therefore, I can't test it. I'm not sure what is missing?
Nz() is for skipping blank records and Val() is to convert each text field into value. Please let me know if this is a correct approach or I need to do something else? Thanks

Answer Source

Okay, after much back and forth, here is the final result that works for this particular problem:

SELECT TOP 1 *, (Nz(Val(IIf([A1_MFI] Is Null, 0, [A1_MFI]))) + Nz(Val(IIf([A2_MFI] Is Null, 0, [A2_MFI]))) + ...) AS TotalScore
ORDER BY (Nz(Val(IIf([A1_MFI] Is Null, 0, [A1_MFI]))) + Nz(Val(IIf([A2_MFI] Is Null, 0, [A2_MFI]))) + ...) DESC

I thought Access allowed field aliases in the ORDER BY, but it doesn't seem to do that any more, if it did at all.