Eliminate duplication of information in database

Don't be so defensive: just because you didn't like the sound of their answers doesn't mean they don't have a point.

You asked for a suggestion, this is my one: your question suggests you don't even have a fundamental understanding of how querying a DB works.  There's  nothing wrong with that: we all started from this situation (a point you've made twice now).  However the onus really is on you to find these things out... I guess why you're asking here.  However simply telling you the answer (which is what you seem to want) is not going to help you at all: you have a lot of learning to do, and it needs to start at a slightly earlier level that the answer to the question you're asking would be.  And getting you up to speed with how to query a DB is not something that can sensibly done on a CF forum.  For argument's sake for me to get where you need to be with SQL required me to do DB101 at polytech: four hours a week for eight weeks (it mighta been 16wks... can't remember it was almost 20yrs ago (shudder)).  Not a coupla forum posts.

So... track down a book on SQL, or some online tutorials, or something like that and get learning.  Dan Bracuk will shortly recommend "Teach yourself SQL in 10min", or whatever it is.  I have never seen it, but he recommends it so often I guess he thinks it's pretty good ;-)

Another thought.  I'm guessing you're in The States.  So, like, there's only 50 states to worry about... and accordingly - as Ian suggested - why are you even making your users type this stuff in?  Pre-populate a DB table with all of them, and offer this as a select on your form.  You don't want to be making your users type this stuff in.  For one thing, what happens if someone types in "Misissippi" (sic).  That's different from "Mississippi".  How's your DB gonna know that?  And you don't want two entries for Misissippi / Mississippi (man: that's a stupid-looking word after typing it four times in rapid succession!).... or a third one for Missisipi... etc.  This is basic UI design: don't make people type stuff in that you should already be providing for them.

And bring thicker skin to the forums in future ;-)

--

Adam

Over 7000 of my 7093 posts to date have been responses, as opposed to asking questions.  Quite often I will ask the same question Owain North asked - What have you tried so far and what part are you having trouble with?

If your nose ever comes back into joint, you might consider answering that.