Results 1 to 5 of 5
  1. #1
    talmrubin is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    4

    Avg Total in Query

    I am running a query on average sales prices over the last 30 days. I am running into an issue where my raw data has line items with a "$0" price that is artificially lowering my averages. Below is a screen show of the query and the column in yellow

    http://imgur.com/AdyiHer


    What can I do to not have it calculate line items with a "$0" unit price when using an Avg Total?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    add <> 0 or > than some number in the unit price field
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    talmrubin is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    4
    I tried this and am getting the same output. I believe when using <>0 is it only nulling the output and not the input data

  4. #4
    talmrubin is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    4
    I tried this and am getting the same output. I believe when using <>0 is it only nulling the output and not the input data

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I believe when using <>0 is it only nulling the output
    No, not if you make it criteria as suggested. You cannot convert the output to nulls when using comparison operators, but I don't know what your statement means with respect to input data. You cannot null that either, whatever it its.

    If you wish to exclude anything that equals zero in a SELECT query, you enter the appropriate expression into the query design grid in that field as criteria. In your Totals query example, you have 3 fields that are criteria fields. Add the Unit Price field again, set the Total row for that field to be WHERE as you have done for the Shipped field and deselect the option to show. It's the same as the shipped field - it has criteria in the form of a date range.

    You may not be getting accurate results because you are using Group By on two fields with criteria yet you chose not to display them. After you fix the current issue, I recommend setting those Group By's for those two fields to WHERE to see if there's a difference. Also, if your Date/Time fields are formatted to include the time, BETWEEN date comparisons will cut off records with time values beyond midnight on any given ending date.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-17-2012, 09:24 AM
  2. Help on Total Query
    By bhofmann in forum Access
    Replies: 5
    Last Post: 04-26-2012, 08:04 AM
  3. Total Query
    By joannakf in forum Queries
    Replies: 1
    Last Post: 12-22-2011, 05:35 PM
  4. Total Query
    By melonwand in forum Queries
    Replies: 24
    Last Post: 10-17-2011, 01:30 PM
  5. Total a Query
    By Bridgid in forum Queries
    Replies: 0
    Last Post: 09-05-2009, 02:51 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