Update with multiple records

[_JSincliar]

I have a table of names and points: Name -- Pts1 -- Pts2 -- etc
I have a second table which I would like to store the sum of all the points in a record.

Table 1
Name1 -- Pts1 -- Pts2
Name2 -- Pts1 -- Pts2

Table 2
Name1 -- TotalPts
Name2 -- TotalPts

I am trying to perform the calculation and update Table 2 for different groups of records. (Name1, Name2, Name 3 are in Group1; Name 4, Name5, Name6 are in Group2). Below calculates the sum of 1 record as an example but I would like an update or insert into query to store the data. There are 29 points fields.

Thanks

SELECT T1_StudentActivities_Tbl.USED_NAME,T1_StudentActivities_Tbl.Group, 
(NZ(T1_StudentActivities_Tbl.[T1_TT_1_Pts], 0) + NZ(T1_StudentActivities_Tbl.[T1_TT_2_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_TT_3_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_TM_1_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_TM_2_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_AthR_1_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_AthR_2_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_AthR_3_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_AthR_4_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_AthR_5_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_AthR_6_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_AthR_7_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_ChBa_1_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_ChBa_2_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_ChBa_3_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_ChBa_4_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_Club_1_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_ArtR_1_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_ArtR_2_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_ArtR_3_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_Committee_1_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_Committee_2_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_Committee_3_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_Committee_4_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_ServR_1_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_ServR_2_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_ServR_3_Pts],0) + NZ(T1_StudentActivities_Tbl.[T1_ServR_4_Pts],0) + 
NZ(T1_StudentActivities_Tbl.[T1_ServR_5_Pts],0)) AS TotalPts
FROM T1_StudentActivities_Tbl;

 

[Mr. B]

Two primary issues need to be dealt with first.

First, your database is not mormalized. You need to rethink your database design. Try creating another to store the scores for each person. There may be more to it that than when you really look at it, but for now, that's a start.

Next, you never store any values that can be calculated. You simple calculate them when you need them.

As you get your database normalized and start to move forward, post back for any assistance.

Just do a search on "normalize" and you should get additional info to help you get started normalizing your data.

 

[gemma-the-husky]

ie you dont need your table 2

when you want these totals, do a query that selects the records you need from table 1 (eg by name and/or by date) and just turn this into a totals query.

 

[_JSincliar]

Thank you for your responses, and sorry for the delay in mine.
@ Mr. B: I am aware that the design I am working with is not ideal and I am willing to work on normalization, however that will have to wait for a time when reports are not needed from this DB.
Normally, I would simply calculate the values as needed or get them through a query, but the calculated values need to be stored so they can be added to later.
ie. Pts1 = 5 Total = 5; next year: Pts1 = 3, Total = 8
I am open to other solutions that will achieve the same result.

I have started by eliminating Table2, (never actually existed as more than a place to test).