Thanks for all the ideas. There were a number of good ideas submitted and I appreciate it. I must not have explained my quandry carefully enough because a couple of replies indicated that. The Departments table only has two fields, DeptID and DeptName. When an administrative user enters a new employee, one of the fields is a dropdown box that the admin can select a department from. There is a button by that field to "Add Department" in case they don't find what they are looking for. The page I am working on can also be reached from the main menu's Add Department option. What I want to do is avoid adding Department names that are really close to others that have already been entered. When a the user tries to enter a Department name that is similar to one that is extant, I want to catch that in the action page and show them the Department names that are similar. They will be able to either abort their insertion if there is already something close enough, or submit it if they still want to. What I ended up doing was suggested by Phil Meadows, a co-worker, who read my post and came over to my desk to share his idea. I do a query to match the first 5 characters of the complete string entered by the user. If that returns more than 5 records, I run another query based on the first 10 characters. That seems to accomplish what I want as well as anything. If anyone wants to see the code I would be happy to send it to them off-line. Thanks again, Loyd Campbell "David L. Penton" <[EMAIL PROTECTED]> on 01/11/2001 10:12:32 AM Please respond to [EMAIL PROTECTED] To: [EMAIL PROTECTED] cc: (bcc: Loyd W Campbell/TX/ONE) Subject: RE: SQL query suggestions
First, do you have a primary key of DeptName (and a separate unique index of DeptId) to prevent duplicates? Second, make a Listbox that has all of the names and let them pick from one of those names or supply their own. If they supply their own, then check the DB to ensure it doesn't exist then insert it, or if it does exist display a message that says it already exists. David L. Penton, MCP Consultant "Mathematics is music for the mind, and Music is Mathematics for the Soul. - J.S. Bach" [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> "If you want an ISP with a great email server and great network access, DON'T use AT&T @HOME" -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of [EMAIL PROTECTED] Sent: Thursday, January 11, 2001 9:49 AM Hi gang, I don't think there is a way to do this, but wanted to give you SQL gurus a shot at it. I have a table that has department names in it (DeptName) with an autogenerated DeptID. When a user wants to enter a new department name, I want to show him or her a list of similar department names to make sure we don't get duplicates entered. Example: User wants to enter West Coast and Midwest Marketing. One entry already in the table is West Coast & Midwest Marketing. If I use SELECT DeptName FROM tblDepartments WHERE DeptName LIKE '%NewDeptName%' It won't find it. I know that on this example I could parse through the variable and look for &s and then use an OR clause, but this is only one case. There could be cases like these: Existing Name Proposed New Name ---------------------------------------------------------------------------- - Marketing/Support Marketing - Support Midwest Admin Midwest Administration Large Corporate Marketing Large Corp Marketing etc. etc. Anyone suggest a "relatively" easy way to look for similar names in the table? Thanks, Loyd Campbell Website Manager Bank One International Group 1700 Pacific Ave., 21st Floor Mail Code TX1-2805 Dallas, TX 75201 [EMAIL PROTECTED] 214-290-2811 (office) 972-567-9082 (cell) ------------------------------------------------------------------------- This email server is running an evaluation copy of the MailShield anti- spam software. Please contact your email administrator if you have any questions about this message. MailShield product info: www.mailshield.com ----------------------------------------------- To post, send email to [EMAIL PROTECTED] To subscribe / unsubscribe: http://www.dfwcfug.org
