William Hindman
dejpolsys at hotmail.com
Wed Aug 24 15:31:39 CDT 2005
..list filters ...your opening form becomes a text box and a list to select from ...you open the form with no recordset ...you user types the first two characters of the last name in the text box ...the change event capture the 2nd letter entry, appends an asterisk to it and requeries the list box using the XX* as your SQL recordset filter ...as the list box fills the user can either select from it to open a new form using opening arguments ...or they can continue adding letters to the text box and the list box requeries each time with a shorter and shorter list to select from. ..I use this approach in most of my mdbs where there are thousands of names to select from but I don't want to pull them all across the network. ..it sounds complicated but once setup, it works like a charm ...I usually place an option group at the top that defaults to what people are commonly looking for but allows them to filter on other sets if needed. ..the table design ...normalization says if its not unique it belongs in a separate table ...we all try but fall short ...but if the focus of the mdb is indictments then you start there ...and you keep it simple ...an INDICTID AN and a type field ...then the entity table ...not names or people because companies and orgs get charged as well as people ...so an ENTID AN and a type ...individual or company or organization, etc. and any other fields that would be unique ...and then an address table, and a "role" type table. ..then you create a join table with the INDICTID and ENTID and add a role type ID and start and end dates. ..the join table weds the Indictment with the Entity and the role they played in the indictment process with dates ...thus if an Entity changes roles during the indictment process, you can capture the change and display it in a tabbed subform without a lot of hassle. ..this is off the top of my head of course but I've built more than a few on that basic structure ...hth :) William ----- Original Message ----- From: "John Clark" <John.Clark at niagaracounty.com> To: <accessd at databaseadvisors.com> Sent: Wednesday, August 24, 2005 2:12 PM Subject: RE: [AccessD] Logic issue > It isn't really based on trials though. It is based on indictment > numbers, and these may or may not go to court...a defendant could plea > out before it hits trial, or charges could be dropped. I think we are on > the same page though, except it seems like you would pull all from this > table, which I would too, but I want to filter it for certain lists > (i.e. I don't want a thousand names showing up when I want to basically > choose from three judges). I don't mind including all names when adding > defendants, because it is probably necessary. > >>>> DElam at jenkens.com 8/24/2005 1:47 PM >>> > I would actually have a table of people that includes everyone. > Second, a > table of trials. Link those two with a third table that has the > trialID, > personID and an ID indicating what role they had in the trial. Given > the > cross connections and likely possibility of multiple people in one role > for > a trial, this is the only scalable solution. > > Debbie > > -----Original Message----- > From: John Clark [mailto:John.Clark at niagaracounty.com] > Sent: Wednesday, August 24, 2005 12:36 PM > To: accessd at databaseadvisors.com > Subject: [AccessD] Logic issue > > > Hi all > > I am having trouble working out how I want to go about something, and > I > am hoping somebody here can give me a nudge. It doesn't sound > difficult, > but I'm running into dead-ends. I am starting to think that I will > just > have to go ahead and finish up with "whatever" and work around things. > > I am doing a project for a district attorney's office, and it will > basically just keep tabs on everybody that passes through the system. > The problem is though that there are "Defendants", defense lawyers, > prosecuting lawyers, judges, and victims, and it isn't rare for a > single > person to cross into multiple lists. And, theoretically, it is > possible > to be in all lists. For instance there are many prosecuting lawyers > that > become defense attorneys, and it is not unlikely that one of these > attorneys could be a judge in the future. That scenario is very > possible, and you can add it that a lawyer is very able to be a > victim, > and hell, we all know they can be criminals ;) Another scenario that > happens very frequently, is that a person is both a defendant and a > victim. > > I don't want the person entering data to scroll through hundreds, and > eventually thousands, of names to pick an attorney's name from among > the > list of everybody else in the system. > > The idea I am working on presently is to add logical fields for each > designation to the table of names. For instances: > > kNameID > txtLastName > txtFirstName > txtMI > txtSuffix > logAttorney > logADA > logJudge > logVictim > logDefendant > > If I do this, I will have removed some fields that are currently > there, > such as: > > txtAddress1 > txtAddress2 > txtCity > txtSt > txtZip > txtPhone > > And, I will put these in another linked table. There may be a need to > have multiple addresses for the defendants, so this would be best I > think. > > The problem that I am foreseeing here...I'm not at that point, so my > fears could be unfounded...is setting these fields to true and/or > false, > as needed. Basically, thinking of victims for a minute here, the > defendant screen, which will actually be an "Indictment" screen, will > have a subform to hold potentially many victims for the indictment. If > a > victim IS already in the system as something else, I will need to tag > that name as a victim and I'm wondering if this will present > difficulties. > > Well, I hope I am being clear enough. If anyone out there has any tips > for me, I would greatly appreciate it if you would pass them along. > > Thank you! > > John W Clark > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > - JENKENS & GILCHRIST E-MAIL NOTICE - This transmission may be: (1) > subject > to the Attorney-Client Privilege, (2) an attorney work product, or (3) > strictly confidential. If you are not the intended recipient of this > message, you may not disclose, print, copy or disseminate this > information. > If you have received this in error, please reply and notify the sender > (only) and delete the message. Unauthorized interception of this e-mail > is a > violation of federal criminal law. > This communication does not reflect an intention by the sender or the > sender's client or principal to conduct a transaction or make any > agreement > by electronic means. Nothing contained in this message or in any > attachment > shall satisfy the requirements for a writing, and nothing contained > herein > shall constitute a contract or electronic signature under the > Electronic > Signatures in Global and National Commerce Act, any version of the > Uniform > Electronic Transactions Act or any other statute governing electronic > transactions. > -- > 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 >