Jürgen Welz
jwelz at hotmail.com
Thu Jan 26 15:24:04 CST 2006
This question is akin to the Library Database structure poser a couple days ago. Checkboxes against roles work, but your giving up some normalization there. A junction table between the Entity and the Role is a more normalized approach. You'll give up a bit of speed as the number of records involved in a query and its index goes up to the number of entities times the average number of roles so the size of the data to be joined goes up in proportion to the additional number of records and of course, the processing of the join involves more processor cycles so the normalization may bite a bit. For better speed you can go with the flatter checkbox approach, and for faster yet, that whole query by join on a bit will get you 8 categories in a single byte field. But I digress... For a law office database with similar considerations, I used a parent form with a lookup combo and some search and navigation buttons that covered all categories. This parent form contained the basic universal information about the person/corporate body. When the user selected a person or corporate body, various tabs became visible, each containing a subform that had records pertaining to that category. Generally, records within a subform category were added from the subform and if no records existed, the subform was simply not visible. The user could pick a type of record from the parent that would force the addition and display of an appropriate subform. For example, if a name happened to be that of a person on whose behalf we had acted, a subform would open displaying the file numbers and the capacity in which the person was related to the file. A subform with related parties showed the corporate bodies to which he was related as well as the capacity in which he is related. If the person was also a client on a real estate transaction and his spouse was on the title, that name showed as spouse. There is another subform that contains Addresses and a further one with Phone contact information. In such a case, the person could be related as a client. If that person was also a lawyer, the related files subform shows file numbers and capacity (opposing counsel, other lawyer on a real estate transaction) and the related parties subform shows his legal firm, assistant, reception... If you happened to have spouse, child or other affiliation, one simply needs to add that person and select a capacity. I did the same thing with addresses and phone numbers. An address could be information about a property being bought or sold, but it is likely also a past or present address of a client effective as at a certain date. The address of a corporation connected as a location address (as opposed to mailing address) would also be the business address of a person associated to the business as an employee or a lawyer. The master address form has subforms showing related parties, the capacity of the relation (home, office..), related files where the property is related to a file (sale, lien, tax appeal) and related phone numbers (If a law firm has the address, a switchboard number is also a number for the lawyers and assistants and individuals can have direct lines as well). The master phone number form has subforms that display addresses (if they exist. For a cell, the address subform would not be visible), parties that may be reached by the number and their association to that number. Using JIT subforms where you set the recordsource on the fly to an appropriately parameterized query makes this real fast. It is easy to control phone number formatting and I allow users to enter phone numbers directly in a subform. Before adding, the system looks up the number and if it exists, displays related parties and the relationship in a pop up. This is useful in determining relationships between parties. Addresses are more difficult in that there are many variations in abbreviations and format. I just have users follow Canada Post guidelines and provide a merge feature to combine related addresses to keep down duplications.Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com > Date: Thu, 26 Jan 2006 15:23:58 -0500> From: John.Clark at niagaracounty.com> > First, I've started two requests for help over the past couple of days,> only to discover the problem, while actually typing the Emails...see,> y'all have learned me some things, since I've been hanging out here.> > Well, it appears my luck may have run out. Actually, I can think of a> couple different ways to do what I am now trying to do, but I want to do> it correctly...something else I've learned here...so I want to run it by> the list members.> > First I'll give you a little background on the program, which is being> written in A2K. It is a case tracking program for our DA's office, and I> have chosen to lump all names together (i.e. lawyers, defendants,> victims, judges, etc.) in a single table and, using checkboxes,> designate what role(s) they fulfill. The purpose of doing this was to> avoid missing people already in the system, in another designation than> they are currently being entered as. It is usefull for them to know if a> defendant has been a victim, or visa versa, and theoratically, a name> can be in the system as all of these designations. I used the scenario> of an ADA, who becomes a defense attorney, then become a judge, then> they are mugged, thus becoming a victim, which makes them lose it and> take revenge on their attacker, which makes them a defendant...they> would then be in all categories...unlikely but possible.> > So, I've got a form called frm_Subjects, which allows entry of names> and their "designations." The main form of the program is the form,> "frmIndictments" and on this form, which basically a case by case entry,> there is a drop down box to choose a defendant, from a list of names.> Using a union query, I have set the top option to be, "".> When they choose this option, I want to take them to the "frm_Subjects"> form. OK, I'm good up to this point.> > The problem is however, can I use this same form, yet have it react> differently? For instance, I've already got it opening up as an "entry> form" with the way I am calling it from the parent form (i.e.> "DoCmd.OpenForm "frm_Subjects", acNormal, , , acFormAdd,> acWindowNormal"). But, when I exit that form it returns to the menu, as> I've told it to do, and as I normally would want it to do. But, when it> is accessed via the other form, I want it to return to that form, and> place the chosen name into the combo box, which is bound to the table.> > The possibilites I've thought of so far are:> > 1) set up a parameter in the On Open event of that form...I don't even> know if this is possible, now that I'm thinking about it...and check for> this when I have it do things. Maybe it defaults to a "0" and works the> way it is now working, but if called from the other form, it gets a "1"> and does things slightly different.> > or 2) Cut my losses and just make a copy of the form and have this copy> do the different actions, and call this one from the other form. I'm> guessing this will be the answer.> > Thank you!> > John W Clark _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/