query field reference help

[-dch]

I am running Access2002 under Windows XP Pro.

I have built a query as a row source for a listbox on a
form. The first version of the query is working fine.

My application requires an optional query for the same
listbox that is only slightly different. I simply
copied/pasted the working query and then attempted to
edit the copy.

These queries have an Expression that returns a value
from a subquery.

In the query version that works, my subquery WHERE
statement refers to one of the query table fields. That
statement looks something like:

....WHERE IOCardPinID = [PinMatesTo]

In the non-working query, I need to replace the
[PinMatesTo] with a reference to another Expression. But
when I do this, the query does not work. If I replace the
reference to the Expression with a hard-coded text
string, the subquery works as intended. I can reference
the same Expression into a new expression and it works
just fine as long as the new expression is not a subquery.

When I say the query does not work, what is happening is
when I run the query, I am asked to input a value for the
expression I am trying to reference - like a parameter
query.

Expression #1:
J1Label: ...returns a string.... ex. "CH0", "CH1"

Expression #2 (does not work - wants value for [J1Label])
J2Label: (SELECT...FROM...WHERE CardLabel=[J1Label]

Expression #2 (this works)
J2Label: (SELECT...FROM...WHERE CardLabel="CH0"

Expression #new (this works fine too)
Exp_New: left([J1Label],2)

How do I reference an Expression in my query into a
subquery inside another expression?

Thanks,

-dch

 

[Michel Walsh]

Hi,



We can't use alias in the WHERE clause. You have to retype the
expression (without alias), or to use, artificially, another level of query:

SELECT temp.*
FROM ( SELECT x, y, x+y As z FROM ... ) As temp
WHERE temp.z= 0


here, the alias z has been promoted to a full field when seen in the outer
query.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you for your suggestions. I had never considered
the use of the SELECT construct in the FROM of an outer
query.

However, when I attempted this - even with a basic test
query, Access said I had a syntax error... I haven't been
able to track that down yet.

In the subquery that is causing me problems, I did try
replacing the reference to the expression with the entire
SQL for that expression. But then Access give me the
error 'At most one record can be returned by this query'.
Not sure of the cause... since the same subquery in its
own expression does not cause the same error.

Thanks for your suggestions, but I am still floundering...

-dch

-----Original Message-----
Hi,



We can't use alias in the WHERE clause. You have to retype the
expression (without alias), or to use, artificially, another level of query:

SELECT temp.*
FROM ( SELECT x, y, x+y As z FROM ... ) As temp
WHERE temp.z= 0


here, the alias z has been promoted to a full field when seen in the outer
query.


Hoping it may help,
Vanderghast, Access MVP

I am running Access2002 under Windows XP Pro.

I have built a query as a row source for a listbox on a
form. The first version of the query is working fine.

My application requires an optional query for the same
listbox that is only slightly different. I simply
copied/pasted the working query and then attempted to
edit the copy.

These queries have an Expression that returns a value
from a subquery.

In the query version that works, my subquery WHERE
statement refers to one of the query table fields. That
statement looks something like:

...WHERE IOCardPinID = [PinMatesTo]

In the non-working query, I need to replace the
[PinMatesTo] with a reference to another Expression. But
when I do this, the query does not work. If I replace the
reference to the Expression with a hard-coded text
string, the subquery works as intended. I can reference
the same Expression into a new expression and it works
just fine as long as the new expression is not a subquery.

When I say the query does not work, what is happening is
when I run the query, I am asked to input a value for the
expression I am trying to reference - like a parameter
query.

Expression #1:
J1Label: ...returns a string.... ex. "CH0", "CH1"

Expression #2 (does not work - wants value for [J1Label])
J2Label: (SELECT...FROM...WHERE CardLabel=[J1Label]

Expression #2 (this works)
J2Label: (SELECT...FROM...WHERE CardLabel="CH0"

Expression #new (this works fine too)
Exp_New: left([J1Label],2)

How do I reference an Expression in my query into a
subquery inside another expression?

Thanks,

-dch

.

 

[Michel Walsh]

Hi,


In all Jet versions, you can use a saved query rather than the "virtual
table", ie:

( SELECT x, y, x+y As z FROM ... )

without parenthesis, can be saved in a query, say q1, then


SELECT *
FROM q1
WHERE q1.z=0


can be use. Only with the most recent versions of Access can you use the
proposed original solution.


Now, why the inner most query does not work? Generally, that error occur
when you use the select query in the select clause:

SELECT a, (SELECT * FROM somewhere)
FROM somewhereElse


since the sub-query returns many records, for each "a" value. You can also
have the problem with

....
WHERE myField=( SELECT a, b, c FROM somewhere)


since a single field cannot be equal to three fields, or


WHERE myField= (SELECT a FROM somewhere)


and the sub-query return many ROWS, then myField=manyRows create a problem.
In that last case, use something like:

WHERE myField = ANY( SELECT a FROM somewhere) ' a universal qualifier

or

WHERE myField=(SELECT MAX(a) FROM somewhere) ' an aggregate without GROUP


or


WHERE myField IN( SELECT a FROM somewhere ) ' an IN or an EXISTS syntax


as example.



It seems you are hit by the very first case:

SELECT a, (SELECT * FROM somewhere)
FROM somewhereElse


since for one, 1, record of somewhereElse, the sub-query based on somewhere
return "n" records. SQL is not sure about what you want to do:

SELECT somewhereElse.a, somewhere.*
FROM somewhereElse, somewhere


is probably what you intend, where each of the n records of somewhere is
glued to each possible record from somewhereElse?




Hoping it may help,
Vanderghast, Access MVP

Thank you for your suggestions. I had never considered
the use of the SELECT construct in the FROM of an outer
query.

However, when I attempted this - even with a basic test
query, Access said I had a syntax error... I haven't been
able to track that down yet.

In the subquery that is causing me problems, I did try
replacing the reference to the expression with the entire
SQL for that expression. But then Access give me the
error 'At most one record can be returned by this query'.
Not sure of the cause... since the same subquery in its
own expression does not cause the same error.

Thanks for your suggestions, but I am still floundering...

-dch

-----Original Message-----
Hi,



We can't use alias in the WHERE clause. You have to retype the
expression (without alias), or to use, artificially, another level of query:

SELECT temp.*
FROM ( SELECT x, y, x+y As z FROM ... ) As temp
WHERE temp.z= 0


here, the alias z has been promoted to a full field when seen in the outer
query.


Hoping it may help,
Vanderghast, Access MVP

I am running Access2002 under Windows XP Pro.

I have built a query as a row source for a listbox on a
form. The first version of the query is working fine.

My application requires an optional query for the same
listbox that is only slightly different. I simply
copied/pasted the working query and then attempted to
edit the copy.

These queries have an Expression that returns a value
from a subquery.

In the query version that works, my subquery WHERE
statement refers to one of the query table fields. That
statement looks something like:

...WHERE IOCardPinID = [PinMatesTo]

In the non-working query, I need to replace the
[PinMatesTo] with a reference to another Expression. But
when I do this, the query does not work. If I replace the
reference to the Expression with a hard-coded text
string, the subquery works as intended. I can reference
the same Expression into a new expression and it works
just fine as long as the new expression is not a subquery.

When I say the query does not work, what is happening is
when I run the query, I am asked to input a value for the
expression I am trying to reference - like a parameter
query.

Expression #1:
J1Label: ...returns a string.... ex. "CH0", "CH1"

Expression #2 (does not work - wants value for [J1Label])
J2Label: (SELECT...FROM...WHERE CardLabel=[J1Label]

Expression #2 (this works)
J2Label: (SELECT...FROM...WHERE CardLabel="CH0"

Expression #new (this works fine too)
Exp_New: left([J1Label],2)

How do I reference an Expression in my query into a
subquery inside another expression?

Thanks,

-dch

.

 

[Guest]

Thanks so much for the SQL help. I actually solved the
problem with a completely different approach - I modified
my data so that all data could use the same (working)
query. But I have saved and printed your information for
future reference.

-dch

-----Original Message-----
Hi,


In all Jet versions, you can use a saved query rather than the "virtual
table", ie:

( SELECT x, y, x+y As z FROM ... )

without parenthesis, can be saved in a query, say q1, then


SELECT *
FROM q1
WHERE q1.z=0


can be use. Only with the most recent versions of Access can you use the
proposed original solution.


Now, why the inner most query does not work? Generally, that error occur
when you use the select query in the select clause:

SELECT a, (SELECT * FROM somewhere)
FROM somewhereElse


since the sub-query returns many records, for each "a" value. You can also
have the problem with

....
WHERE myField=( SELECT a, b, c FROM somewhere)


since a single field cannot be equal to three fields, or


WHERE myField= (SELECT a FROM somewhere)


and the sub-query return many ROWS, then

myField=manyRows create a problem.

In that last case, use something like:

WHERE myField = ANY( SELECT a FROM somewhere) ' a universal qualifier

or

WHERE myField=(SELECT MAX(a) FROM somewhere) ' an aggregate without GROUP


or


WHERE myField IN( SELECT a FROM somewhere ) ' an IN or an EXISTS syntax


as example.



It seems you are hit by the very first case:

SELECT a, (SELECT * FROM somewhere)
FROM somewhereElse


since for one, 1, record of somewhereElse, the sub- query based on somewhere
return "n" records. SQL is not sure about what you want to do:

SELECT somewhereElse.a, somewhere.*
FROM somewhereElse, somewhere


is probably what you intend, where each of the n records of somewhere is
glued to each possible record from somewhereElse?




Hoping it may help,
Vanderghast, Access MVP

Thank you for your suggestions. I had never considered
the use of the SELECT construct in the FROM of an outer
query.

However, when I attempted this - even with a basic test
query, Access said I had a syntax error... I haven't been
able to track that down yet.

In the subquery that is causing me problems, I did try
replacing the reference to the expression with the entire
SQL for that expression. But then Access give me the
error 'At most one record can be returned by this query'.
Not sure of the cause... since the same subquery in its
own expression does not cause the same error.

Thanks for your suggestions, but I am still floundering...

-dch

-----Original Message-----
Hi,



We can't use alias in the WHERE clause. You have

to
retype the

expression (without alias), or to use, artificially, another level of query:

SELECT temp.*
FROM ( SELECT x, y, x+y As z FROM ... ) As temp
WHERE temp.z= 0


here, the alias z has been promoted to a full field

when
seen in the outer

query.


Hoping it may help,
Vanderghast, Access MVP


I am running Access2002 under Windows XP Pro.

I have built a query as a row source for a listbox on a
form. The first version of the query is working fine.

My application requires an optional query for the same
listbox that is only slightly different. I simply
copied/pasted the working query and then attempted to
edit the copy.

These queries have an Expression that returns a value
from a subquery.

In the query version that works, my subquery WHERE
statement refers to one of the query table fields. That
statement looks something like:

...WHERE IOCardPinID = [PinMatesTo]

In the non-working query, I need to replace the
[PinMatesTo] with a reference to another Expression. But
when I do this, the query does not work. If I

replace
the

reference to the Expression with a hard-coded text
string, the subquery works as intended. I can reference
the same Expression into a new expression and it works
just fine as long as the new expression is not a subquery.

When I say the query does not work, what is

happening
is

when I run the query, I am asked to input a value

for
the

expression I am trying to reference - like a parameter
query.

Expression #1:
J1Label: ...returns a string.... ex. "CH0", "CH1"

Expression #2 (does not work - wants value for [J1Label])
J2Label: (SELECT...FROM...WHERE CardLabel= [J1Label]

Expression #2 (this works)
J2Label: (SELECT...FROM...WHERE CardLabel="CH0"

Expression #new (this works fine too)
Exp_New: left([J1Label],2)

How do I reference an Expression in my query into a
subquery inside another expression?

Thanks,

-dch


.

.

 

[blah]

Hi,


In all Jet versions, you can use a saved query rather than the "virtual
table", ie:

( SELECT x, y, x+y As z FROM ... )

without parenthesis, can be saved in a query, say q1, then


SELECT *
FROM q1
WHERE q1.z=0


can be use. Only with the most recent versions of Access can you use the
proposed original solution.


Now, why the inner most query does not work? Generally, that error occur
when you use the select query in the select clause:

SELECT a, (SELECT * FROM somewhere)
FROM somewhereElse


since the sub-query returns many records, for each "a" value. You can also
have the problem with

....
WHERE myField=( SELECT a, b, c FROM somewhere)


since a single field cannot be equal to three fields, or


WHERE myField= (SELECT a FROM somewhere)


and the sub-query return many ROWS, then myField=manyRows create a problem.
In that last case, use something like:

WHERE myField = ANY( SELECT a FROM somewhere) ' a universal qualifier

or

WHERE myField=(SELECT MAX(a) FROM somewhere) ' an aggregate without GROUP


or


WHERE myField IN( SELECT a FROM somewhere ) ' an IN or an EXISTS syntax


as example.



It seems you are hit by the very first case:

SELECT a, (SELECT * FROM somewhere)
FROM somewhereElse


since for one, 1, record of somewhereElse, the sub-query based on somewhere
return "n" records. SQL is not sure about what you want to do:

SELECT somewhereElse.a, somewhere.*
FROM somewhereElse, somewhere


is probably what you intend, where each of the n records of somewhere is
glued to each possible record from somewhereElse?




Hoping it may help,
Vanderghast, Access MVP

Many thanks from 8 years hence.