Arthur- You can't make Access perform miracles. It can do exact matches easily. It can do certain "pattern" matches using LIKE. If "Butanol" is supposed to match "1-Butanol", then I suppose you could try LIKE - but you're likely to get duplicates or mismatches.
SELECT ListOfMyChemicals.* FROM ListOfMyChemicals, ListedChemicals WHERE ChemicalName LIKE "*" & [Product Name] & "*" That will also be *very* slow for thousands of records. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Arthur Sent: Tuesday, August 16, 2005 1:48 PM To: ms_access@yahoogroups.com Subject: RE: [ms_access] Listing all entries inTableA that have an entry in Table B John - no - the ID's do not match - and Part of a name might match Part of a name in the "other" list. for example butanol in one list - = 1-Butanol in the other list. Fields are ChemicalName in one list and [Product Name] in the other list. At 08:17 AM 8/15/2005 -0500, you wrote: >Arthur- > >For any given chemical, does MYID in ListofMyChemicals match ListedID >in ListedChemicals? If so, you should be able to find the matches with >a simple inner join: > >SELECT ListOfMyChemicals.* >FROM ListOfMyChemicals >INNER JOIN ListedChemicals >ON ListOfMyChemicals.MYID = ListedChemicals.ListedID > >If you can't match on ID, then what fields tell you the rows are the same? > >John Viescas, author >"Building Microsoft Access Applications" >"Microsoft Office Access 2003 Inside Out" >"Running Microsoft Access 2000" >"SQL Queries for Mere Mortals" ><http://www.viescas.com/>http://www.viescas.com/ > > >-----Original Message----- >From: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED] On >Behalf Of Arthur >Sent: Monday, August 15, 2005 6:10 AM >To: ms_access@yahoogroups.com >Subject: [ms_access] Listing all entries inTableA that have an entry in >Table B > >Help please - >I have a listing of some thousand chemicals and would like access to >list only those found on another listing of 5000 chemicals. >ListofMYchemicals - >and ListedChemicals >are the two tables, each with numerous fields, including an MYID and >ListedID ListofMYChemicals will have many entries of the same chemical > >the Final list of CommonEntries should have each of MY entries that are >listed in the big ListedList. >yikes - is that understandable??? I hope so - Thanks for looking at it >- and let me know if there is anything else you might need to "get me started" >Arthur > >Flickr Photos - www.flickr.com/photos/nycarthur/ > > >[Non-text portions of this message have been removed] > > > >------------------------ Yahoo! Groups Sponsor --------------------~--> ><font face=arial size=-1><a >href="<http://us.ard.yahoo.com/SIG=12hka1tt4/M=362131.6882499.7825260.1 >510227>http://us.ard.yahoo.com/SIG=12hka1tt4/M=362131.6882499.7825260.1 >510227 >/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1124111464/A=2889191/R=0/SIG=10r9 >0krvo /*<http://www.thebeehive.org>http://www.thebeehive.org >">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet >Life - brought to you by One Economy</a>.</font> >--------------------------------------------------------------------~-> > > >Yahoo! Groups Links > > > > > > > >SPONSORED LINKS ><http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Micro >soft+access+database&w2=Microsoft+access&w3=Microsoft+access+database+d >esign&w4=Microsoft+access+database+training&w5=Ms+access&c=5&s=146&.sig >=Cpg-bVzzToxMgBaoPndL1Q>Microsoft access database ><http://groups.yahoo.com/gads?t=ms&k=Microsoft+access&w1=Microsoft+acce >ss+database&w2=Microsoft+access&w3=Microsoft+access+database+design&w4= >Microsoft+access+database+training&w5=Ms+access&c=5&s=146&.sig=ycV_TzgG >MU05ZXKjvUH90A>Microsoft access ><http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+design&w >1=Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+dat >abase+design&w4=Microsoft+access+database+training&w5=Ms+access&c=5&s=1 >46&.sig=9BNAfs0RtZNz9ikScwfvrg>Microsoft access database design ><http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+training >&w1=Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+d >atabase+design&w4=Microsoft+access+database+training&w5=Ms+access&c=5&s >=146&.sig=VKKa1NC4QjjtlNpuWptuGg>Microsoft access database training ><http://groups.yahoo.com/gads?t=ms&k=Ms+access&w1=Microsoft+access+data >base&w2=Microsoft+access&w3=Microsoft+access+database+design&w4=Microso >ft+access+database+training&w5=Ms+access&c=5&s=146&.sig=0RCNgoa3eX7ChUE >6AtiGjw>Ms access > > >---------- >YAHOO! GROUPS LINKS > > * Visit your group "<http://groups.yahoo.com/group/ms_access>ms_access" on the web. > * > * To unsubscribe from this group, send an email to: > * <mailto:[EMAIL PROTECTED]>ms_access- [EMAIL PROTECTED] > * > * Your use of Yahoo! Groups is subject to the <http://docs.yahoo.com/info/terms/>Yahoo! Terms of Service. > > >---------- Photopage - http://www.arthure212.bravepages.com/Index.html Photosig - http://www.photosig.com/go/users/userphotos?id=95581 [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12h4jgi2p/M=362329.6886308.7839368.1510227 /D=groups/S=1705115370:TM/Y=YAHOO/EXP=1124225313/A=2894321/R=0/SIG=11dvsfulr /*http://youthnoise.com/page.php?page_id=1992 ">Fair play? Video games influencing politics. Click and talk back!</a>.</font> --------------------------------------------------------------------~-> Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12h926dlu/M=362329.6886308.7839368.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1124225784/A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?page_id=1992 ">Fair play? Video games influencing politics. Click and talk back!</a>.</font> --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
