I second this - even though I posted a selotape solution, to break down the info into 2 separate entities is the better option
-----Original Message----- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: 13 May 2002 17:24 To: CF-Talk Subject: RE: can this be done? > I have an existing project where a form takes a field "name" > and inserts in in a row "name" in an Access database. > > So, we have about 1000 names in the database as one name > (firstname lastname). Now, the client changes his mind, and > wants to be able to sort by last name, firstname. > > Yes, this was discussed prior to, and was deemed not needed. > > Is there a way, to somehow break up the full name into 2 fields, > say by using the space as a separator? I would only need to do > this once as it is an event. Could I make two new fields, > last_name and first_name, then get the fullname, break it up > using the space, then insert first_name, then last_name? > > Is this even possible? This would have been so much easier if > client said yes to this at the design stage! Yes, this is certainly possible. Access has the necessary string functions to support this within SQL statements, and you could approach this in many ways - you could do a one-time, offline data transformation, and rewrite your code to work with the new table design, or you could incorporate the transformation process into your existing CF code. I'd recommend the first option, myself. I'll also take this opportunity to say that, despite the client's initial desires, there's no reason that you had to use one field, even if that's how the client wanted to see the data. It's a good idea, for exactly this reason, to break your data into its smallest components, so that you don't have to transform it later. Even if your client didn't want to see the two fields as separate, you could certainly have handled that in your CF code, and still had the two separate fields in the database. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
