Querry not given right results

  • Thread starter Thread starter juan
  • Start date Start date
J

juan

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


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
Click to expand...
 
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


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
Click to expand...

.
Click to expand...
Click to expand...
 
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


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


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
Click to expand...

.
Click to expand...
Click to expand...
Click to expand...
 
Hello Tom,
sorry for the late reply but busy as always. Just want to
thank you for the help you provided. I think the first is
what I'm looking for. If I have any problems will post.

Once again thanks alot.

juan
-----Original Message-----
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


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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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


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
Click to expand...
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
Click to expand...
there
a
way around this?
Hope I'm clear. Please advise any input would really
appreciate it.
Thanks,
Juan

.
Click to expand...
Click to expand...

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top