Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Lightbulb Unanswered: "Sql" Is it possible!!

    What am I trying to do?

    For each area (Area_No) by day (Date_Val) I want to establish if the Actual value (Act_Val) of sales for a given product (Prod_ID) is higher than the target value (Tgt_Val). This is signified by a 1 in TargetYes if it does or a 1 in TargetNo if it does not. There is an Inner Join (Ref_No) to ensure only specific areas are included. The result of this query gives me 1200 results with 20 different dates for each of the 60 Areas.
    Qry_2 then adds up all the areas that do or do not achieve target for each date giving 20 results.
    I have had to include various checks on Act_Val and Tgt_Val as there are multiple null or 0 value results that mess up my results.
    This query works perfectly. My problem is that I have to combine these two queries into one. All my efforts have resulted in failure.
    Please advise if there is either or..
    1. A better way of achieving the same result in one query
    2. A way to combine the queries

    Qry_1
    SELECT Table_A.Date_Val, IIf(Sum([Act_Val]) Is Null Or Sum([Tgt_Val]) Is Null Or Sum([Tgt_Val])=0 Or Sum([Tgt_Val])>Sum([Act_Val]),0,1) AS TargetYes, IIf(Sum([Act_Val]) Is Null Or Sum([Tgt_Val]) Is Null Or Sum([Tgt_Val])=0 Or Sum([Tgt_Val])>Sum([Act_Val]),1,0) AS TargetNo
    FROM Table_A INNER JOIN Table_B ON Table_A.Ref_No = Table_B. Ref_No
    WHERE (((Table_A.Prod_ID)>=20 And (Table_A.Prod_ID)<=26
    GROUP BY Table_A.Date_Val, Table_B.Area_No
    ORDER BY Table_B. Area_No;

    Qry_2
    SELECT qry_1.Date_Val, Sum(qry_1.TargetYes) AS SumOfTargetYes, Sum(qry_1.TargetNo) AS SumOfTargetNo
    FROM qry_1
    GROUP BY qry_1.Date_Val;

    DATA TYPES!!
    Date_Val = Date
    Act_Val = Integer
    Tgt_Val = Integer
    Ref_No = Integer
    Area_No = integer
    Prod_ID = integer
    Ref_No = integer

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try this:
    Code:
    SELECT qry_1.Date_Val
         , Sum(qry_1.TargetYes) AS SumOfTargetYes
         , Sum(qry_1.TargetNo) AS SumOfTargetNo
    FROM (
    	SELECT Table_A.Date_Val
    	     , IIf(Sum([Act_Val]) Is Null
    	        Or Sum([Tgt_Val]) Is Null 
    	        Or Sum([Tgt_Val])=0 
    	        Or Sum([Tgt_Val])>Sum([Act_Val]),0,1) AS TargetYes
    	     , IIf(Sum([Act_Val]) Is Null
    	        Or Sum([Tgt_Val]) Is Null 
    	        Or Sum([Tgt_Val])=0 
    	        Or Sum([Tgt_Val])>Sum([Act_Val]),1,0) AS TargetNo
    	  FROM Table_A INNER JOIN Table_B 
    	    ON Table_A.Ref_No = Table_B.Ref_No
    	 WHERE (((Table_A.Prod_ID)>=20 And (Table_A.Prod_ID)<=26
    	 GROUP BY Table_A.Date_Val, Table_B.Area_No
    	 ORDER BY Table_B. Area_No
         ) qry_1
    GROUP BY qry_1.Date_Val;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2004
    Posts
    2
    Thanks. That worked a treat. I was struggling around how to pass the subquery values to the main query. None of the SQL books I have deal with this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •