If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query field reference help
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 |
#2
|
|||
|
|||
query field reference help
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 "-dch" wrote in message ... 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 |
#3
|
|||
|
|||
query field reference help
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 "-dch" wrote in message ... 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 . |
#4
|
|||
|
|||
query field reference help
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 wrote in message ... 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 "-dch" wrote in message ... 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 . |
#5
|
|||
|
|||
query field reference help
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 wrote in message ... 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 "-dch" wrote in message ... 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 . . |
Thread Tools | |
Display Modes | |
|
|