Link to home
Start Free TrialLog in
Avatar of APD Toronto
APD TorontoFlag for Canada

asked on

Filtering Out $0 Totals

Hello Experts,

I have a query along the following lines:

SELECT Table1.fldSalesPerson AS SalesPerson, 

Sum(IIf([Table1]![fldProduct]<>"B",[Table1]![fldQty],0)) AS QtySoldOfA, 

Sum(IIf([Table1]![fldProduct]<>"A",[Table1]![fldQty],0)) AS QtySoldOfB, 

Sum(IIf([Table1]![fldProduct]="A",1,IIf([Table1]![fldProduct]="C",0.33,0))*[Table1]![fldCost]) AS CostSoldOfA, 

Sum(IIf([Table1]![fldProduct]="B",1,IIf([Table1]![fldProduct]="C",0.67,0))*[Table1]![fldCost]) AS CostSoldOfB

[CostSoldOfA]+[CostSoldOfB] AS TotalSold

FROM Table1
GROUP BY Table1.fldSalesPerson

HAVING Table1.fldDate BETWEEN #01/01/2012# AND #01/31/2012#;

Open in new window


I will buld this, however, within VBA as a much larger string depending on the form criteria that the user chooses (or doesn't chose).  I will store this in a variable called strSQL1.

In addition, the user may choose to select a second date range, and this will be stored in strSQL2, the query syntax would be exactly the same, except for the very last line being

HAVING Table1.fldDate BETWEEN #01/01/2011# AND #01/31/2011#;

Open in new window


If, however, the user doesn't choose the second date range, strSQL2 will be "" (empty)

With this said, my question is how can I avoid displaying rows of 0s on my report?  In other words, I'd like to add to my HAVING clause the following:

HAVING (Table1.fldDate BETWEEN #01/01/2011# AND #01/31/2011#)
   AND (TotalSold > 0);

Open in new window



BUT, I do not want to filter these Sales people out from strSQL1, if they are in strSQL2, and vise-versa, but also remembering that strSQL2 maybe "", which would make the above AND valid.

In summary, I need to avoid $0s in both strSQL's, both need to return the same # of rows, all unless strSQL2 isn't empty.

Any help in pointing me in the right direction will be greaful.

Thank you!
Avatar of jerryb30
jerryb30
Flag of United States of America image

APD,
A sample database with dummy data, and the form would be very helpful. Additionally, a couple of samples of expected results would help.
For me, Access 2000/2003 is my limit.
Jerry
Avatar of Ryan
Wow, either that's a lot of conditionals, or you've written that out to seem like a lot. The question is really long.

IF you're just building an SQL string in code, you can just test for empty string and not append it?

SQL1 = SQL1 & "HAVING Table1.fldDate BETWEEN #01/01/2011# AND #01/31/2011#"
'NOTE I removed that semicolon at the end-its not needed anyway
if SQL2<>"" then
  SQL1 = SQL1 & SQL2
end if

I can't tell what else you may be asking, but I suspect a SELECT DISTINCT  might be useful, and maybe also a UNION.
Avatar of APD Toronto

ASKER

OK, let me simplify my question...



Say I have the following data

Person      Sales      Date
A      5.00      1/5/2012
A      5.00      1/7/2012
B      12.00      2/1/2012
C      3.00      2/1/2011
D      20.00      1/8/2010

SELECT Person, SUM(Sales) AS totalSales
FROM tbl
GROUP BY Person
HAVING Date BETWEEN #1/1/2011# AND #12/31/2011#

Open in new window


Executing this query will result:

Person      TotalSales
C      3.00

If we alter the above query's year to 2012 instead of 2011, we'd get:

Person      TotalSales
A      10.00
B      12.00

My question is how can i Make sure if a person falls in one they must fall in the other, so query 1 would produce

Person      TotalSales
A      0.00
B      0.00
C      3.00

and query2 will produce


Person      TotalSales
A      10.00
B      12.00
C      0.00

Notice person D is in niether.

The UNION SELECT suggestion also lead me, i could also have my results as


Person      2011Sales      2012Sales
A      0.00            10.00
B      0.00            12.00
C      3.00            0.00

I basically need 1 query, or 2 queries with the same number of rows. I'm just not sure how to start?  With the last query, i now figure I can use IIf BETWEEN... AS 2011Sales, but I worry if this will be efficient as my table exceeds 10,000 records?

Any feedback will be appreciated.
Something like the following?
select person
      ,sum(iif(datepart("yyyy",sdate) = 2011,sales,0)) as sales2011
      ,sum(iif(datepart("yyyy",sdate) = 2012,sales,0)) as sales2012
from tbl
where datepart("yyyy",sdate) in (2011,2012)
group by person;
awking, that won't slow down performance?
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
APD_Toronto,

Again, a sample database would make this a lot easier to understand.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
thanks