Nested Select Statements in Access 1

[oscarse]

Tek-tips search engine is down ... I am attempting to use functions to import filter data in a query and was thinking I should be able to use a nested Select Statement in the form;

Select foo("a") as strA, foo("b") as strB, foo("c") as strC
{Select alpha, beta, capa from xyz where alpha like strA and beta like strB and capa like strC;};

is my syntax incorrect or is Access just not able to do nested select statements?

 

[PHV]

What about this ?
SELECT foo("a") AS strA, foo("b") AS strB, foo("c") AS strC
, alpha, beta, capa
FROM xyz
WHERE alpha Like foo("a") AND beta Like foo("b") AND capa Like foo("c")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[oscarse]

OK I am using something like this now but believe every instance of foo is being called for every instance of xyz which would not be a good thing.

What I would like to get to is that the foo values get set once and then used to filter each instance of xyz ... I am now trying a compound Query as Qry1 which loads all the foo values as 1 record and then use that to filter Qry2 ... still not working properly.

Not familiar with crosstab or pivot queries so not sure if maybe these are meant to provide this type of functionality?

I normally program in a RDBMS that makes these types of queries seem simple ... when I try to do the same things in SQL it always makes my brain hurt

 

[PHV]

As foo is called with CONSTANTs as argument (at least in your posted example) it will be called only 3 times at the beginning, despite the number of rows in xyz.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886