John Clark
John.Clark at niagaracounty.com
Tue May 3 07:43:43 CDT 2005
Thanks for your response Jim. Sorry it took so long for me to get to it...they have actually been making me work for my money here lately. Can you believe it...a govt. employee working for their pay? Absurd! Seriously though, I have been really busy on many non-access things...IP Telephony, Spam filtering, network, and some stupid medical billing program that we house on our UNIX box that has no support...AHHHHHH!!! I think for this one, I'll whip it out the simple way, only because I am pressed for time. But, I think what you have explained is what I've had in my head for another program. I have a program on deck for our District Attorney's office that this...or a form of it...may be handy for. When I program...and I'm sure most of you are the same...I try to account for anything that COULD happen, unlike the users who usually respond with something like, "that rarely happens." BUT IT CAN, AND SOMETIMES DOES, HAPPEN! Anyhow, in the DA's case, I ran into a circumstance, which actually has happened, in many different forms. I have to list defendants (I have to refrain from calling them "criminals"), officers, lawyers (both defense and prosecutor), judges, and victims, for a given case. However, it has actually happened, from what I understand, that a victim had also been a defendant in another case. I forget the specifics, but I guess the focus of the case would have been different if they'd known this at that time. It was overlooked, because you have many cases, and these are overseen and handled by different lawyers and clerical staff. I want to flag something like this in my system. Theocratically, and not all that far fetched, a single person can be in the system with many different "labels." For instance lets say I am a defense attorney that goes to work for the DA, or visa versa, which does frequently happen. I am assaulted or robbed one day, and I retaliate myself and assault someone. I am now in the system as both a defense attorney and a prosecutor, as well as a victim, and finally a defendant. In the previous programs that we looked at, this wasn't really addressed. So, I am planning to have a "names" table that would be linked to a "types" table, which would link a single person with all of their pertinent "designations." This would save space and time by providing single entry for these people, and by having a SubForm viewed with their name, and maybe some sort of "flag," there status would be easily viewed. I think my biggest problem is going to be ascertaining whether a person is in there already, in a quick easy way. I'm thinking of Having them type the name, and bringing up a list of matches, if any exist. Well, now that I've totally went off on a tangent, thanks again for your input. John W. Clark >>> jimdettman at earthlink.net 4/28/2005 8:08 PM >>> <<Should I have an individual table for each descriptor? This is the way I mostly do it now. I put in value lists for something like "gender," which will most likely stay the same (I don't want to think about the "what ifs" here). But for other things, I like to leave it open for the user to input. I then give them a button for "Program Administration" and a bunch of little forms, which are basically prettied-up continuous forms, to enter these options. >> That is one way. Another is a single lookup table: tblLookup LookupID - Autonum PK LookupType - Long FK to tblLookupTypes Value tblLookupTypes LookupTypeID - Autonum PK Description UserModifable - True/False I do most of my systems this way. But another way to design something like this is: tblPeople - One record per person PersonID - Autonumber PK FirstName LastName etc. tblAttributeTypes - One record per possible attribute type AttributeID - Autonumber PK Description tblPeopleAttributes - One record per person/attribute PersonID - Long FK to tblPeople AttributeID - Long FK to tblAttributeTypes Value Now on the above, *some* might like to add a PeopleAttributeID - Autonum PK and if so, knock yourself out, but I think it's rather silly. With the above a user can add a new attribute they want to keep track of i.e. "Nose pierced" or "Distance between eyes" and then assign a value for each person. You can also extend this design by adding additional fields to tblAttributeTypes that defines the allowed value (i.e. Y/N, 1 - 99999, Lookup list, etc). If a lookup list you would add a table (tblAttributeTypeValues) that defines a list of allowed values for a given attribute. HTH, Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Clark Sent: Thursday, April 28, 2005 2:36 PM To: accessd at databaseadvisors.com Subject: [AccessD] Access 101 question (I think) I am whipping up a quick program, and there are many "descriptors" in the main table (i.e. hair, eyes, status, race, etc.). My question is this: Should I have an individual table for each descriptor? This is the way I mostly do it now. I put in value lists for something like "gender," which will most likely stay the same (I don't want to think about the "what ifs" here). But for other things, I like to leave it open for the user to input. I then give them a button for "Program Administration" and a bunch of little forms, which are basically prettied-up continuous forms, to enter these options. This actually came in handy, on one of my previous programs. Their "Race" designations were altered, about a year after they began using the program...asian was split into sub-groups...and they were able to go in and change this without my help. I was just wondering whether this is considered acceptable practice, or if I am being a bit retentive here. TIA John W Clark -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com