how to create a function w/in a query?

[ash]

Using Access ('07 I have now - but doesn't matter) - lets say i have 2
tables

Table1, with fields a,b,c
Table2, with fields h,i,,j

I want a query that does something like this:

select a, b, c, function( select j from Table2 where c =h)
from Table1


Something like that. Basically, I know how to do this in oracle. I
can call an oracle function within a select statement, and bring some
value back from another table. I can even pass in a value from the
select, into the function - if I recall correctly. Anyhow, I want to
know how i can do this with Access. (I'm using Access 2007 - but i dont
think the queries are much different from '03). I'm somewhat new to
access queries, so any help or pointing me to a site that shows this,
would be great!

Ash

 

[John Vinson]

Using Access ('07 I have now - but doesn't matter) - lets say i have 2
tables

Table1, with fields a,b,c
Table2, with fields h,i,,j

I want a query that does something like this:

select a, b, c, function( select j from Table2 where c =h)
from Table1


Something like that. Basically, I know how to do this in oracle. I
can call an oracle function within a select statement, and bring some
value back from another table. I can even pass in a value from the
select, into the function - if I recall correctly. Anyhow, I want to
know how i can do this with Access. (I'm using Access 2007 - but i dont
think the queries are much different from '03). I'm somewhat new to
access queries, so any help or pointing me to a site that shows this,
would be great!

Ash

Oracle and SQL/Server let you mix programming language constructs with
SQL queries... but Access doesn't. What you can do is create a
function in VBA, by creating a new Module; and you can call that
function from a Query. You can pass *values* - strings, numbers, etc.
- but (other than passing a SQL string and having code in your
function that parses it) you can't pass a SQL statement.

John W. Vinson[MVP]

 

[ash]

thanks John; wow thats a steep learning curve for something i need to
do like, yesterday.

i avoid subqueries (functions much more elegant), but maybe there's no
way in Access to even create a subquery and pass in a value from the
main query to the subquery, so that it returns a single value to the
main query?
Surely other people must have this problem when analyzing tables...
there must be something more elegant than programming modules everytime
you need to do query a 2nd table, and pass back something - based on
criteria from the first query (?)

 

[John Spencer]

You might be able to do what you want (at least in some cases) by using
the VBA Domain functions: DLookup, DMax, DMin, DFirst, DLast, DAvg.

DLookup("J","Table2","H=" & C)

IF H and C are not numeric fields then you will also need to concatenate
in quote marks.

DLookup("J","Table2","H=""" & C & """")

 

[John Vinson]

i avoid subqueries (functions much more elegant), but maybe there's no
way in Access to even create a subquery and pass in a value from the
main query to the subquery, so that it returns a single value to the
main query?

Yes, you can create a subquery that returns a single value and
references the main query. Since I know nothing about the structure of
your tables or the nature of the function that you want to perform, I
can't give you a specific solution; but something like

SELECT main.This, main.That, (SELECT anothertable.whatever FROM
anothertable WHERE anothertable.linkfield = main.linkfield AND <other
criteria>) AS whatever FROM main...


John W. Vinson[MVP]