Re: Querry not given right results

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 08/16/04 

Date: Mon, 16 Aug 2004 12:59:28 -0500

Dear Juan:

I think maybe you want this:

SELECT [PART#], COST, QT_PRICE, ZPR1, ZPRC, ZQCM
  FROM TABLE1
  WHERE [PART#] IN
    (SELECT [PART#] FROM TABLE1
      WHERE STATUS2 IN ("K", "C", "Z") AND SALES LIKE "B*"
        AND SATYPE = "BID"
      GROUP BY [PART#]
      HAVING COUNT(*) >= 3)
      GROUP BY [PART#])
  ORDER BY [PART#]

I asked you whether the list of PART#s was given by the subquery
above, but I don't see where you answered that question.

Or, do you want to filter out the rows in a different way:

SELECT [PART#], COST, QT_PRICE, ZPR1, ZPRC, ZQCM
  FROM TABLE1
  WHERE [PART#] IN
    (SELECT [PART#] FROM TABLE1
      GROUP BY [PART#]
      HAVING COUNT(*) >= 3)
      GROUP BY [PART#])
  WHERE STATUS2 IN ("K", "C", "Z") AND SALES LIKE "B*"
    AND SATYPE = "BID"
  ORDER BY [PART#]

In this way, the criteria on STATUS2, SALES, and SATYPE apply only to
the outer query, not on the inner query filtering PART#.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Sun, 15 Aug 2004 23:31:31 -0700, "Juan" <jhernan562@aol.com> wrote:

>Hello Tom,
>sorry for the delay been busy. Here's exactly what I
>would like to do is a group count query where part is
>given over 3 times plus my other criteria.
>My fields:
>Part# Cost QT_Price ZPR1 ZPRC ZQCM Status2 Sales Satype
>
>But I want only parts that count given >= 3 the status
>in K, C, Z Sales = Like "B*" Satype= BID
>
>So the sql I was given a while back does give me those
>parts that appear over three times but it does not
>exclude the other criteria.
>I guess I can do a group count query first by just taking
>the part number and then make another query adding the
>table and the query and joint the part number and then
>exclude the criteria.
>Hope I make sense. Please advise any input.
>
>Thanks,
>Juan
>
>>-----Original Message-----
>>Dear Juan:
>>
>>Not having a description of what you want this to
>accomplish, it's a
>>bit difficult. But I'm seeing something I find peculiar.
>>
>>Let's concentrate on the subquery starting in line 4.
>>
>>This introduces a new instance of the table TABLE1, and
>aliases it as
>>T. Then, within that subquery you reference the
>instance of TABLE1
>>from the outer query. I'm thinking you probably want to
>reference the
>>instance of TABLE1 in the inner query.
>>
>>That's because the subquery usually needs to stand
>alone, except if it
>>needs to reference the correlated row in the outer
>query. With an IN
>>clause, you probably don't even want any reference
>outside the inner
>>query.
>>
>>Have you written this inner subquery and made it work?
>If so, please
>>post it here.
>>
>>Is it this:
>>
>>SELECT [PART#]
>> FROM TABLE1
>> GROUP BY [PART#]
>> HAVING Count([PART#]) >= 3
>> AND STATUS2 In ("K","C","Z")
>> AND SALES Like "B*"
>> AND SATYPE = "BID"
>>
>>This would indicate you want to see only those parts
>which have more
>>than 2 rows whose status2 is K, C, or Z, whose sales
>start with B, and
>>whose satype is "BID". Running this subquery separately
>should give
>>you this list. Does it?
>>
>>When you transform this into a subquery for the IN
>clause, and you
>>alias TABLE1 in it to distinguish it from the instance
>of TABLE1 in
>>the outer query, you must change all the references to
>TABLE1 in this
>>inner subquery to be T thoughout the subquery. Making
>references to
>>the outer instance of TABLE1 which I see in the query
>you posted would
>>create a logic I cannot begin to digest. But I sure the
>computer
>>does, which would create results that would likely be
>unexpected.
>>
>>I strongly recommend you alias both the table in the
>outer query and
>>the one in the subquery. Make them, for example, T1,
>and T2. That
>>way you cannot reference TABLE1 at any other place in
>the query. By
>>aliasing both of them, you must use an alias at every
>point in the
>>criteria, and you will probably reference them
>correctly. Not
>>aliasing one of them may leave some ambiguity in your
>mind.
>>
>>Anyway, that's my theory about what I'm guessing you
>probably
>>intended. Using logic like that currently in your query
>is so
>>uncommon I can only guess it is not what you intended.
>>
>>Any help here? I hope so! Please get back on what
>happens.
>>
>>Tom Ellison
>>Microsoft Access MVP
>>Ellison Enterprises - Your One Stop IT Experts
>>
>>
>>On Fri, 13 Aug 2004 14:11:17 -0700, "juan"
><Jhernan562@aol.com> wrote:
>>
>>>Hello all,
>>>have following sql view query:
>>>
>>>SELECT TABLE1.[PART#], TABLE1.COST, TABLE1.QT_PRICE,
>>>TABLE1.ZPR1, TABLE1.ZPRC, TABLE1.ZQCM
>>>FROM TABLE1
>>>WHERE (((TABLE1.[PART#]) In (SELECT [PART#] FROM TABLE1
>AS
>>>T
>>>GROUP BY [PART#]
>>>HAVING Count([PART#]) >= 3)) AND ((TABLE1.STATUS2) In
>>>("K","C","Z")) AND ((TABLE1.SALES) Like "B*") AND
>>>((TABLE1.SATYPE)="BID"))
>>>ORDER BY TABLE1.[PART#];
>>>But I'm not getting exactly what I want from the HAVING
>>>criteria. It seems that the query returns parts that
>match
>>>one or more of those criteria.Example,
>>>I get two parts
>>>1232
>>>1232
>>>But my count is >= 3, so I shouldnt' get these. But
>since
>>>these 2 parts have SATYPE (BID) I get them. So is there
>a
>>>way around this?
>>>Hope I'm clear. Please advise any input would really
>>>appreciate it.
>>>Thanks,
>>>Juan
>>
>>.
>>