Getting Access to do those sorts of things is always hideously difficult. If you really need to stick with a single query, you can use a view -- iirc Access calls them Queries. The view would include all the data from the issues table, with the owner column modified as such
SELECT *, left(owner + ',', inStr(owner,',')-1) as ownername FROM issues then in your cf query you would be able to use a normal join on that added column to the users.name column. SELECT * FROM v_issues, users WHERE v_issues.ownername = users.name s. isaac dealey 954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > 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 > Your ad could be here. Monies from ads go to support these > lists and provide more resources for the community. > http://www.fusionauthority.com/ads.cfm > Unsubscribe: http://www.houseoffusion.com/cf_lists/uns > ubscribe.cfm?user=633.558.4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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
