Results 1 to 3 of 3
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181

    SQL - removing zero results from query

    I need some help using SQL to ignore results that have a zero value.



    Attached is an example of my database. CPU Help2.zip

    Currently I have three lines of product: A, B, and C.
    Due to lack of historical volume data, these products are being compared to current volume of A, B, and C deliveries.
    I would like to compare Cost Per Unit (CPU) to current volume to create a weighted average.

    However, sometimes there are current volume results with no past CPU. When I try to create a weighted average, the volume is there, but not the rate. This skews the average too low.

    What I would like to do is have a query set up to ignore zero results per product line in a similar form of logic:
    * Return ["A" CPU]>0 when [B CPU]>=0 and [C CPU]>=0 (give me all [A CPU]>0, B and C do not matter)
    * Return ["B" CPU]>0 when [A CPU]>=0 and [C CPU]>=0 (give me all [B CPU]>0, A and C do not matter)
    * Return ["C" CPU]>0 when [A CPU]>=0 and [B CPU]>=0 (give me all [C CPU]>0, A and B do not matter)

    I tried writing this into a SQL statement:
    Code:
    WHERE ([Data].["A" CPU]>0) AND ([Data].["B" CPU]>=0) AND ([Data].["C" CPU]>=0))
    OR ([Data].["B" CPU]>0) AND ([Data].["A" CPU]>=0) AND ([Data].["C" CPU]>=0))
    OR ([Data].["C" CPU]>0) AND ([Data].["A" CPU]>=0) AND ([Data].["B" CPU]>=0));
    That seemed to have no effect though. Changing the ORs to ANDs eliminated all results.

    Is this possible, or am I thinking about this the wrong way?
    Last edited by kagoodwin13; 05-11-2012 at 03:22 PM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Quote marks in field names is bad idea. Advise no spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. Better would be A_CPU, B_CPU, C_CPU.

    Data is not normalized, multiple similar field names is an indicator, and that may be what is making this difficult. Why are you saving calculated values (Total) into table?

    The aggregate calcs would work if 0s where nulls. Following gets your desired values (note changed field names):
    SELECT Sum(IIf([A_CPU]=0,Null,[A_Total]))/Sum(IIf([A_CPU]=0,Null,[A_Volume])) AS [A wgt avg CPU], Sum(IIf([B_CPU]=0,Null,[B_Total]))/Sum(IIf([B_CPU]=0,Null,[B_Volume])) AS [B wgt avg CPU], Sum(IIf([C_CPU]=0,Null,[C_Total]))/Sum(IIf([C_CPU]=0,Null,[C_Volume])) AS [C wgt avg CPU]
    FROM Data;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181
    Quote Originally Posted by June7 View Post
    Quote marks in field names is bad idea. Advise no spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names. Better would be A_CPU, B_CPU, C_CPU.

    Data is not normalized, multiple similar field names is an indicator, and that may be what is making this difficult. Why are you saving calculated values (Total) into table?

    The aggregate calcs would work if 0s where nulls. Following gets your desired values (note changed field names):
    SELECT Sum(IIf([A_CPU]=0,Null,[A_Total]))/Sum(IIf([A_CPU]=0,Null,[A_Volume])) AS [A wgt avg CPU], Sum(IIf([B_CPU]=0,Null,[B_Total]))/Sum(IIf([B_CPU]=0,Null,[B_Volume])) AS [B wgt avg CPU], Sum(IIf([C_CPU]=0,Null,[C_Total]))/Sum(IIf([C_CPU]=0,Null,[C_Volume])) AS [C wgt avg CPU]
    FROM Data;
    This worked! Thank you!

    My actual database is more normalized and doesn't have totals as data, but I included them for the example. Thanks for the tips on not using quote marks and spaces - it was actually breaking this query for a bit until I switched to underscores.

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

Similar Threads

  1. Query contents are deleted after exporting the query results
    By alfcee in forum Import/Export Data
    Replies: 6
    Last Post: 11-13-2012, 09:35 AM
  2. Replies: 6
    Last Post: 05-14-2012, 07:24 AM
  3. Removing duplicates
    By DAVID W in forum Access
    Replies: 5
    Last Post: 12-21-2011, 03:15 PM
  4. Need help removing duplicates
    By warlock in forum Queries
    Replies: 1
    Last Post: 04-14-2011, 03:44 PM
  5. Removing parameters
    By katrinanyc926 in forum Reports
    Replies: 1
    Last Post: 08-04-2010, 04:24 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