Results 1 to 2 of 2
  1. #1
    asmith533 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2012
    Posts
    13

    Calculated Column in a Table

    How to you write a formula for a calculated field where you don't have to fill in all the values of the fields used in the calculation. Line 1 will always be filled out but there may not be a value in any of the other fields of the calculation. Here is my example:

    Calculated Field = OrderTotal

    The value of the field is (Line1Total + Line2Total + Line3Total + Line4Total + Line5Total + Shipping)

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    A few things:

    Though newer versions of Access allow you to put calculated fields directly in the table, it is generally not advised. Most robust database programs (like SQL) do not allow this, and if your database ever got so big that you needed to convert it to a better database program, you would run into issues. It is usually best (and recommended) to do all of your calculations at the Query level and not at the Table level.

    Also, if all those fields appear in the same record, it is probably indicative of a Database that is not Normalized. While you can work work with databases that are not Normalized, it can make things much harder for yourself done the road. You should probably have separate records types for each line item, so that each record only has a single amount field.

    Here is a good article on database design that talks about those type of things: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

    All that notwithstanding, in regards to your question, when trying to add fields of which some may be blank or not have a record, you can use the NZ function to handles those blanks/Nulls and convert them to zero so you can do the math, i.e.
    Code:
    OrderTotal: NZ(Line1Total,0) + NZ(Line2Total,0) + NZ(Line3Total,0) + NZ(Line4Total,0) + NZ(Line5Total,0) + NZ(Shipping,0)
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated Column
    By zbaker in forum Queries
    Replies: 1
    Last Post: 01-07-2015, 03:38 PM
  2. Calculated Columns Same Table And "Column Scrubbing"
    By defaultuser909 in forum Queries
    Replies: 10
    Last Post: 07-19-2012, 11:42 AM
  3. Replies: 3
    Last Post: 03-14-2012, 06:48 AM
  4. Calculated column in a report
    By beanhead0321 in forum Reports
    Replies: 2
    Last Post: 08-16-2011, 08:48 PM
  5. creating a calculated column
    By bold01 in forum Access
    Replies: 13
    Last Post: 02-10-2011, 08:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums