That is where 'webguy' had it - he just had the AS clause reversed. Am I missing something?
---------- Original Message ---------------------------------- From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 11:44:41 -0500 >I'm not sure but try puting the "left(issues.owner, inStr(issues.owner, >',')-1)" up in the select as "left(issues.owner, inStr(issues.owner, ',')-1) >as LastName". That should get rid of the error. > > >-----Original Message----- >From: Jeff Chastain [mailto:[EMAIL PROTECTED]] >Sent: Friday, January 17, 2003 11:19 AM >To: CF-Talk >Subject: RE: Access SQL Question - Round 2 > > >No, that is along the lines of what I have been trying without any luck. > >Okay, I have the following tables and data: > >table: issues (id, owner) > 1 Doe > 2 Klein, Doe > 3 James > 4 Doe, James > >table: owners (name, email) > Doe [EMAIL PROTECTED] > James [EMAIL PROTECTED] > Klein [EMAIL PROTECTED] > >The results I need to get back are ... > (issue.id, owners.name, owners.email) > 1 Doe [EMAIL PROTECTED] > 2 Klein [EMAIL PROTECTED] > 3 James [EMAIL PROTECTED] > 4 Doe [EMAIL PROTECTED] > >Where the name and email come from the first (or only) name in the issues >table. > >I would not think that this would be that hard, but for some reason it >appears to be. > >Thanks >-- Jeff > > > >---------- Original Message ---------------------------------- >From: "webguy" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >Date: Fri, 17 Jan 2003 15:56:19 -0000 > >>No access guru, (anymore:-/) but try >> >>SELECT Firstinlist as (or "=" ???)left(issues.owner, inStr(issues.owner, >>',')-1) >>FROM issues, users >>WHERE Firstinlist = users.name >> >>Something like that... >> >>WG >> >>> -----Original Message----- >>> From: Jeff Chastain [mailto:[EMAIL PROTECTED]] >>> Sent: 17 January 2003 15:44 >>> To: CF-Talk >>> Subject: WOT: Access SQL Question - Round 2 >>> >>> >>> Okay, I am still having problems with this ... so here is an attempt to >>> simplify the problem. >>> >>> I have a hand-me-down Access database which I am trying to get some data >>> out of. >>> >>> I have a table called issues with a field called owner. The owner field >>> may contain a single name, or it may contain a comma-separated list of >>> names. >>> >>> I have a table called users with a field called name. I am needing to >>> join the two tables up based on the first or only name in the >>> issues.owner field matched to the users.name field. >>> >>> Whenever I try using inStr in the WHERE clause, access throws an invalid >>> procedure error. Anybody got any suggestions? >>> >>> This does not seem to work with Access and even if it did, it would only >>> work for records with a list of users in the issues table, not just a >>> single entry .... >>> >>> SELECT * >>> FROM issues, users >>> WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name >>> >>> >>> Thanks >>> -- Jeff >>> >>> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
