Results 1 to 4 of 4
  1. #1
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48

    selecting fields that have data

    Hi,



    I need to query a table, that has two numeric fields, but only one of them has value, the other is empty(The table has value either in the amount or in the quantity field).
    I need to get the sum of the ones that is no empty, so the problem is that I don't know which field do I need to target. Is there a way to make a query for this?
    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Can't you sum BOTH fields in the query?
    nulls will = 0.

    if the sum= null,then convert with NZ(field,0).
    select sum(nz(fields1,0)), sum(nz(field2,0)) from table

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use the Nz function and include both fields

    Code:
    =Sum(Nz(Amount,0))+Sum(Nz(Quantity,0))
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    harryklein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    48
    Thank you I guess this is what I was looking for.

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

Similar Threads

  1. Replies: 12
    Last Post: 01-30-2018, 10:35 AM
  2. Replies: 1
    Last Post: 08-15-2015, 01:30 AM
  3. Selecting highest value across several fields
    By megabrown in forum Queries
    Replies: 19
    Last Post: 10-28-2014, 01:16 AM
  4. Replies: 2
    Last Post: 12-26-2013, 02:50 PM
  5. Replies: 3
    Last Post: 07-03-2012, 02:01 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