Link to home
Start Free TrialLog in
Avatar of MIKI RUN

asked on

Sum Field in Same Row in ACCESS 2013

Have       TABLEWEEK  with fields

mon     tue    wed   thu   fri  sat     weektotal
 1             1                  1          1
                 1         1      1          1      

each day diferent value
i need to sum theses fields but in a row so i get the week total
how can i get it ? in access 2013
 any help please!!
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

You don't sum values and store them in a Table, but you can create a Query that will sum those values:

SELECT mon, tue, wed, thu, fri, sat, mon+tue+wed+thu+fir+sat AS weektotal FROM YourTable

If any of those fields could contain a NULL value, you can use the Nz function:

SELECT Nz(mon,0), Nz(tue,0) etc etc, Nz(mon,0) + Nz(tue,0) +blah blah AS weektotal FROM YourTable
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MIKI RUN


Thanks !!!
No points please.  The others have given you the bullets for your gun.

This set of columns is what is known as a "repeating group".  Each of the 7 values should be stored in a separate row in a child table.  If you read about database normalization, First Normal form discusses why repeating groups are poor practice.  In addition, you will find that since Access is a relational database rather than a spreadsheet, it has no functions that operate "across" columns in a table.  All functions operate "down" rows in a recordset.  this means that when you violate First Normal form, you are essentially on your own.  You will be writing more complex queries than would otherwise be needed and you'll probably need code also.  And, unless you are going to resolve the problem sooner rather than later, you should probably create custom VBA functions to replicate Excel functions.