Darrell Burns
dhb at flsi.com
Wed Jun 1 12:37:49 CDT 2011
John, Little did you know that your "2 quick questions" would turn into a battle over intelligent keys and rage on for 6 days. Holy smokes! It seems like you're doing the right thing by making the PK an Autonumber. As for creating an additional "key" for searching, that's not something I typically do with Name/Address data because it's so unreliable due to data entry mistakes, misspellings, nicknames, etc. You may want to consider a "search for" text box where the user can enter some portion of the name/address/DOB, with wild card characters. Then plug that phrase into a SQL query and return one or more suspects. What you'll be querying against is a hash field composed of the full name/address/DOB elements; eg, MurphyJosephTJoe102Main20010601 (note that I inserted his nickname, so he'll be found whether searching for Joe or Joseph). My CRM databases actually include dictionaries of nicknames and misspellings, but that might be overkill for your app. As for the YYYYMMDD format for dates, I think someone else responded accurately that it's consistent across countries, has a fixed length, and sorts naturally (youngest to oldest or vice versa). And it should be stored as a string for searching. HTH, Darrell -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark Sent: Friday, May 27, 2011 7:35 AM To: 'Access Developers discussion and problem solving' Subject: [Spam]8.31 Re: [AccessD] [Spam]8.31 Re: 2 quick questions >>> "Darrell Burns" <dhb at flsi.com> 5/26/2011 3:53 P >>> Hi John. Basic rule of database design...don't use intelligent keys. In other words, a key that may change over time (eg, Joe gets adopted and changes his surname) or may be incorrect (eg, Joe's birthday was entered as 49 instead of 94), or could be duplicated (Joe's twin sister Joelle would have the same key). I deal with name/address data all the time and I always create a unique sequential number (eg, Access's Autonumber) as a customer ID. You may want to create a Lookup key, such as the one you proposed, which can be indexed and used for searching. On a fairly small database, you can build an index on the full lastname+firstname+DOB. *** You bring up good points, but this is all the info I have to work with. But, I will probably change to include more of the first name, and maybe even the last name too. *** I do use the autonumber in the table; I am just using this one for searching and tracking purposes. *** If '49 was entered for the birthday it would kick it out, because the person is too old for the survey. But, I understand your point, and in my case it is a minimal risk of error...there is immediate feedback on age, so it would probably stick out, if it weren't close. And, I may build in some validation features as well. *** the adoption...or change of use of MI...did occur to me, but I really don't have many other options. And the DOB should always be in YYYYMMDD order. *** Why? Seriously...I am not challenging your claim, but I'd really like to learn about this. What does the order really matter? It isn't a big deal to change this, but before I do things like this I like to know why...so w/I am asked I don't just confess to being a blind follower. As for the full-screen forms, go into Access options (big fat button in upper left corner, then small button on bottom of dialog), select Current Database, then check "Overlapping Windows". At the individual form level, there's a property called "Fit to screen"...make sure it's set to No. *** Thank you...I did not know this and it was driving me nuts. HTH, Darrell ----- -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com