Results 1 to 7 of 7
  1. #1
    arthura is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2009
    Posts
    8

    Adding Two Columns

    I have two columns of data that I want to add together. When I add the columns, I get output only when both columns contain data. I want output for all of the rows even if both columns don't contain data.



    This is what I am getting:
    Column A Column B Column C
    2 4 6
    3
    5
    6 2 8

    This is what I want:
    Column A Column B Column C
    2 4 6
    3 3
    5 5
    6 2 8

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Are these fields in the same table? If so you could likely get what you want with a calculated field in a query and the Nz function to replace nulls with 0.

    e.g.,
    ColumnC: Nz([ColumnA],0) + Nz([ColumnB],0)

  3. #3
    arthura is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2009
    Posts
    8
    Thanks! This fixed my problem!

  4. #4
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Glad it worked.

    I find it helpful to think that when doing math, Access interprets nulls as 'I don't know'.

    Thus, if someone were to ask you to add the number 3, and 'I don't know', what would your answer be?

    Nulls are never interpreted as 0 unless you specify it using the Nz function.

  5. #5
    tinytree is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    28
    Quote Originally Posted by CraigDolphin View Post
    Glad it worked.

    I find it helpful to think that when doing math, Access interprets nulls as 'I don't know'.

    Thus, if someone were to ask you to add the number 3, and 'I don't know', what would your answer be?

    Nulls are never interpreted as 0 unless you specify it using the Nz function.
    my question is this:may I set some conditions in the talbe,like excel.for example:in excel I can set a3=a1+a2,after I have recorded a1,a2,a3 eaqul a1 added a2.in the access table,it have three fields (a1,a2,a3).how can i do? I try to set a3 condition =nz(a1,0)+nz(a2,0).but it has error .

  6. #6
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Tinytree,

    you cannot perform calculations in an access table. They are strictly for storing data.

    If you wish to create a set of rows and columns that include both data, and calculations, then what you need is a query.

    So, let's say you have a table called tblMyTable that contains a field A1, and a field A2.

    You would create a query that sources its data from tblMyTable, and returns A1, and A2, and also has a third calculated field that we'll call A3. A3 contains the calculation you mentioned.

    So, the SQL for your query might look something like:
    Code:
    Select A1, A2, Nz([A1],0) + Nz([A2],0) As A3
    From tblMyTable;

  7. #7
    tinytree is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    28
    Thank you very much.

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

Similar Threads

  1. Combining columns into rows
    By steeveepee33 in forum Queries
    Replies: 5
    Last Post: 04-30-2009, 09:18 PM
  2. Multiple Price Columns
    By kmwhitt in forum Access
    Replies: 0
    Last Post: 04-04-2009, 09:48 PM
  3. sub-reports and columns
    By blg in forum Reports
    Replies: 0
    Last Post: 02-21-2009, 11:11 PM
  4. Crosstab: Sum of Columns over Row
    By tommaccoy in forum Queries
    Replies: 0
    Last Post: 12-03-2008, 05:26 AM
  5. Add Columns to query
    By 4petessake in forum Access
    Replies: 0
    Last Post: 06-15-2007, 01:38 PM

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