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!!

Scott McDaniel (EE MVE )

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

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MIKI RUN

ASKER

Thanks !!!

PatHartman

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.