Results 1 to 3 of 3
  1. #1
    Andy001 is offline Novice
    Windows 8 Access 2007
    Join Date
    Feb 2015
    Posts
    3

    Query Sum only sums where same data fields appears


    Hi,

    I have the function below in my main query but it only sums the same fields that appear in both my sub queries leaving the other fields blank.

    Sum([TotalOrdersSHPT1]+[TOtalOrdersSHPT2])

    I have two shipments so my first query reflects the product Ids and total quantity found in shipment1 while the other query reflects what we've had for shipment2. Because both queries have the same field "product Ids", I've created a third query to group both queries by Product Id and sum the total quantity received. And although my query reflects the product IDs received, it only sums product Id fields that are found in both shipment1 and shipment 2, leaving the other fields blank.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try wrapping each field in the Nz() function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Andy001 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2015
    Posts
    3
    I did as you instructed but I received the error message below.

    "The expression you entered has a function name that Microsoft Access can't find."

    Not sure if me using SharePoint apps is the reason for this.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-06-2014, 08:48 AM
  2. Combine Sums of fields with same first column
    By mjdizenzo in forum Queries
    Replies: 23
    Last Post: 06-13-2012, 12:09 PM
  3. Query that sums data
    By accessnovice78 in forum Queries
    Replies: 3
    Last Post: 04-12-2012, 11:46 AM
  4. query that sums the data for me
    By citygov in forum Queries
    Replies: 4
    Last Post: 08-18-2011, 03:25 PM
  5. Replies: 2
    Last Post: 05-05-2011, 12:41 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