Re: Querry not given right results

• Previous message: SteveS: "Re: displaying data by month"
• In reply to: Tom Ellison: "Re: Querry not given right results"
• Next in thread: Tom Ellison: "Re: Querry not given right results"
• Reply: Tom Ellison: "Re: Querry not given right results"
• Messages sorted by: [ date ] [ thread ]

Date: Sun, 15 Aug 2004 23:31:31 -0700

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
>
>.
>

• Previous message: SteveS: "Re: displaying data by month"
• In reply to: Tom Ellison: "Re: Querry not given right results"
• Next in thread: Tom Ellison: "Re: Querry not given right results"
• Reply: Tom Ellison: "Re: Querry not given right results"
• Messages sorted by: [ date ] [ thread ]