Isacc's solution gives me what I need - thanks for all the input. I won't get into the debate about Access vs. SQL. However, Access does have an advantage over SQL in small, light use, cases ($$$), but a well designed DB is much easier to deal with, no matter what system it is in.
Thanks -- Jeff ---------- Original Message ---------------------------------- From: S. Isaac Dealey <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 12:06:30 -0500 >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
