I need to sum several fields in row by name

[Fozzy9767]

I have a table that has multiple fields with names that end in a specific string, e.g. "net" and "exp". I need to come up with a way to add all the 'net" fields in a row, as well as all the "exp" fields. These two totals will be updating the last two fields in the table. I'm looking for a way that doesnt involve hand typing 43 fields into a query. Any advice is appreciated.

 

[MajP]

Sql:
SELECT tblSum.ID, sumExpNet([ID]) AS [Sum]
FROM tblSum;

Function:
Public Function sumExpNet(intID As Long) As Long
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Set rs = CurrentDb.OpenRecordset("tblSum", dbOpenDynaset)
  rs.FindFirst "ID =" & intID
  For Each fld In rs.Fields
    If Right(fld.Name, 3) = "net" Or Right(fld.Name, 3) = "exp" Then
      sumExpNet = sumExpNet + fld.Value
    End If
  Next fld
End Function

 

[Fozzy9767]

Thanks, that got me on the track, now I just need to tell it to ignore the last field. That's the one to be updated and it is null untill then. It also happens to end in "net".

 

[Fozzy9767]

Ok, I'm doing something wrong here.

Do While Not rs.EOF
    For Each fld In rs.Fields
    If Right(fld.Name, 3) = "exp" Then
    TotAuth = TotAuth + fld.Value
    ElseIf Right(fld.Name, 3) = "net" Then
    TotNetAuth = TotNetAuth + fld.Value
    End If
    Next fld
    rs.Edit
    rs![TotalAuth] = TotAuth
    rs![TotalAuthNet] = TotAuthNet
    rs.Update
    rs.MoveNext
    
Loop

As you can see I need 2 separate totals, but they are not coming out correctly.

 

[Fozzy9767]

I figured it out. I needed to reset my variables to 0 on each pass. It's always the little things.

 

[SkipVought]

Hi,

I have a table that has multiple fields with names that end in a specific string, e.g. "net" and "exp".

Your REAL problem seems to be that your table is not normalized.

Check out

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fneily]

SkipVought is right. Your table is violating the first normal form (step). Since you are adding those fields together, and since you have them all with the same endings, then they seem to be a category - or in Access terms, a table. You have a table within a table - not allowed.
And why are you storing calculations? Again not normalized.

 

[Fozzy9767]

Sorry, I don't have time to explain the complexities of what I have to work with. Normalization is NOT an issue. You shouldn't jump to conclusions without facts. That response is such a knee-jerk reaction here.

 

[dhookom]

Relax Fozzy9767. Both Skip and fneily used the word "seem(s)". I think their replies were both valuable and they should be thanked for the observation rather than accused of a "knee-jerk reaction".

You might have a good reason for a seemingly un-normalized table structure but someone else in your position might need some assistance and instruction. That's what these fora are all about, helping each other.

In the future, you might want to begin your first post with something like "I know this seems un-normalized..." It doesn't take much time and would probably fend off any comments regarding your structure. And when someone attempts to provide additional assistance to you or any of the 1,000s of people who read this thread you should be thankful.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

I have a table that has multiple fields with names that end in a specific string, e.g. "net" and "exp".". Fact.

"These two totals will be updating the last two fields in the table.". Fact.

Normalization named and developed by Ted Codd. Fact.

"Normalization is NOT an issue.". False Premise.

"complexities of what I have to work with.". Subjective and emotional.

Quantitative Logic. I highly recommend. I also instruct on a graduate level under the field Analytical Philosophy.
I met Codd while at MIT.
Also did some stat work for B.F. Skinner in his last year at Harvard in 1974. You may be interested in his Radical Behavior theory.