Sub query combining fields

[Guest]

Anyone know how to do a sub query combining fields in one. Such as

select * from table1 where (fname & " " & lname) = (select fullname from
provpref)

One table has a fullname field while the other has first and last names. Do I
need an IN statement. Anyone know the correct syntax. (I'm afraid of getting a
Cartesian product. The DB has about 9,000 records so that would be time
consuming to display.

Thanks

Jim S
(e-mail address removed)

 

[Michel Walsh]

Hi,


You can use an IN right after the =, before the (


You can also use

SELECT DISTINCTROW table1.*
FROM table1 INNER JOIN provpref
ON ( table1.fname & " " & table1.lname = provpref.fullname )



but you won't be able to edit the query in the graphical section of the
query designer, with that solution.

The DISTINCTROW is not required if there is no duplicated values in
provpref.fullname.



Hoping it may help,
Vanderghast, Access MVP