I have this table:

tblStore

with these fields:

storeID (autonumber)
storeName
locationOrBranch

and this table

tblPurchased

with these fields:

purchasedID
storeID (foreign key)
itemDesc


In the case of stores that have more than one location, there is a problem when two people inadvertently key the same store location differently. For example, take Harrisburg Chevron. On some of its receipts it calls itself Harrisburg Chevron, some just say Chevron at the top, and under that, Harrisburg. One person may key it into tblStore as storeName Chevron, locationoOrBranch Harrisburg. Person2 may key it as storeName Harrisburg Chevron, locationOrBranch Harrisburg. What makes this bad is that the business's name is Harrisburg Chevron.

Is there some kind of search I could put on the control that works like the firefox awesome bar (suggests all matching letter combinations from anywhere in the word, with more frequently used ones near the top) that would suggest the already-entered name to the user?

With an eye to accommodating user inadequacies, is the smart-search-and-suggest the best programmatic solution? I feel that making more rules and more inconvenient data entry policies should be plan B. Plan A should be to take what the user gives and programmatically help them (or force them) to make the right choice.

(So, respectfully, please don't suggest policy changes, or such, as solutions. I am 100% aware of those and don't need recommendations on them. As I said, those measures are Plan B. Please, if you can't help with Plan A, please don't reply because I don't need any assistance with Plan B.)

Thanks for your help.

Anybody have any ideas?

"Please, if you can't help with Plan A, please don't reply because I don't need any assistance with Plan B."

After they enter the first 3 letters you could do a search using wildcards to find a match. Specifically I would search for where the store name contained the first 3 letters after a space or started with the first 3 letters. The syntax in SQL-Server would be SELECT * FROM tblStore WHERE storename LIKE '% XXX%' OR storename LIKE 'XXX%'.

The problem with this is that if you have a lot of stores it won't be fast. I would go with Plan B myself, but ......

Oh, and this question has nothing to do with database design. You might have gotten a better answer posting under a different area.

TBH, I don't really understand what Plan B is. So forgive me if this is Plan B....

In short, there is a 1-many relationship for stores and locations. So should you not simply model that in your design? Implicit in your design is that ID is the PK and enforced, but that there is no natural key (you know you should always enforce a natural key right?).

So I would split these off into two tables.

The awesomebar bit is UI. I don't know how to do this however I imagine you would get best performance using AJAX techniques querying the table a little like Mark suggests. The question is how clever you want to get (e.g. account for distance in order to overcome typos) and what performance you require.

Simplest might be to just have them select an existing store, with an option to add one if it does not already exist.

pootle flump ur codings are working excelent.

After thinking about my answer above I came up with a slightly faster but harder to maintain possibility.

You could build a new table that contains three columns. The first column would be each word in the name of a store, the second column would contain the full name of the store, and the third would contain the ID of the store. So for a store called "Oxbow Lake 2" you would have three entries in this table:

"Oxbow", "Oxbow Lake 2", 217
"Lake", "Oxbow Lake 2", 217
"2", "Oxbow Lake 2", 217

Now as they type you can search this table using the LIKE statement and bring up suggestions. But every time a store name changes you would have to delete the entries in this table and build new ones.