Allen Browne’s Name table design
in [Forms]
|
From: Dennis on 20 May 2010 20:31 Hi, I'm on Access via Office XP Pro running on Windows 7. I'm working on a simplified version of Allen Browne's People / Name table design. I'm finally working on a church database. I did not implement the entire design. Instead, I implement a two tier table design. The first table is tblFamily and the second tier is tblFamilyMembers. Database structure ----------------------------------------- The structure of the tblFamily is: FamilyId Access auto assigned number. FamilyName The family's name. This can be either the head of the household's first and last name or just the family's last name. That is up to the user. Mailing Name This is the name that will appear on any mailing labels that are generated to the family as a whole. Address City St Zip The structure of the tblFamilyMember table is: MemberNo Access auto assigned number. FirstName Member's first name MidInit Member middle initial LastName Member last name. It defaults to the family last name from the Family Name. I have a “on the fly” field that I build in a query call MemName which is First, Middle, and Last concatenated together. I also have a “on the fly” field called MemLastFirst which is the member's Last Name, First Name, and Mid Init concatenated together. Background ------------------------------------------------ Currently I have three name search combo boxes: 1. Family Name 2. Mailing Name 3. Member Name (using either MemName or MemLastFirst – users choice) When the users go searching for a name, they may have to look in upto three different places / boxes. My Questions / Goal ------------------------------------------------- I would like to have a SINGLE Name combo box that would allow the user to: 1. Ideally search on all three names at once. 2. Failing that, I would at least like to be able to search on both the Mailing Name and Member Name simultaneously. Any ideas on how to do this? I think I need to join the two files somehow and then create a single field from the Mailing Name and the Member Name, but I'm not quite sure how to go about that. Since this might be both a database and a forms programming question, I'm going to try to post it in both communities. We will see if this works. Thanks for you help. Dennis
From: Allen Browne on 20 May 2010 23:46 Dennis, I'm not sure which people/name table you're referring to as I have hundreds of web pages up, but did you see this one: http://allenbrowne.com/AppHuman.html In that design, the families (households/groupings/corporate entities) are in the *same* client table as the individuals. Part of the reason for that design choice is that it avoids the kinds of searching/matching problems you are struggling with. With that structure, the search is on just a single table, and typically just one or two fields of that table (MainName and FirstName), individually, or in combination. I guess you could create a search form that uses your query with the 3 concatenated-name fields, and provide a search form with an unbound text box where the user enters the name to find and you filter to form to records that have a match in any of the 3 concatenated fields. You could build the filter string that way, but it seems to me much more work for a less accurate result. For example, if the user types John as the name to find, you get false positives that weren't intended (Johnson etc.) If the guy types John Smith, but your concatenated field reads John W Smith, you won't find the guy. Using a single table for both corporate an individual clients might seem more complex, but I don't think you have "simplified" your task here. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:4AC24945-3C95-47DF-8EF8-DBCC2FE1C319(a)microsoft.com... > Hi, > > I'm on Access via Office XP Pro running on Windows 7. > > I'm working on a simplified version of Allen Browne's People / Name table > design. > > I'm finally working on a church database. I did not implement the entire > design. Instead, I implement a two tier table design. The first table is > tblFamily and the second tier is tblFamilyMembers. > > Database structure ----------------------------------------- > > The structure of the tblFamily is: > > FamilyId Access auto assigned number. > FamilyName The family's name. This can be either the head of the > household's first and last name or just the family's last name. That is > up > to the user. > Mailing Name This is the name that will appear on any mailing labels > that > are generated to the family as a whole. > Address > City > St > Zip > > > The structure of the tblFamilyMember table is: > > MemberNo Access auto assigned number. > FirstName Member's first name > MidInit Member middle initial > LastName Member last name. It defaults to the family last name from > the Family Name. > > > I have a “on the fly” field that I build in a query call MemName which is > First, Middle, and Last concatenated together. I also have a “on the fly” > field called MemLastFirst which is the member's Last Name, First Name, and > Mid Init concatenated together. > > Background ------------------------------------------------ > > Currently I have three name search combo boxes: > 1. Family Name > 2. Mailing Name > 3. Member Name (using either MemName or MemLastFirst – users choice) > > > When the users go searching for a name, they may have to look in upto > three > different places / boxes. > > > My Questions / Goal ------------------------------------------------- > > I would like to have a SINGLE Name combo box that would allow the user to: > > 1. Ideally search on all three names at once. > 2. Failing that, I would at least like to be able to search on both the > Mailing Name and Member Name simultaneously. > > Any ideas on how to do this? > > I think I need to join the two files somehow and then create a single > field > from the Mailing Name and the Member Name, but I'm not quite sure how to > go > about that. > > Since this might be both a database and a forms programming question, I'm > going to try to post it in both communities. We will see if this works. > > > Thanks for you help. > > > Dennis > > >
From: Dennis on 21 May 2010 02:23 Allen, I guess it is time to re-read your article. I will do that tomorrow. Thanks for the input. Dennis
From: Allen Browne on 22 May 2010 08:57 "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:403FA6DC-66EB-413D-9780-5F0BBCE7F68E(a)microsoft.com... > I guess it is time to re-read your article. I will do that tomorrow. > Thanks for the input. Dennis, there is another option. If you *never* need a person to belong to more than one group, you can put a foreign key in the same table. Enter the families and persons into the one table, and just include a FamilyID in that table. Then for any person, that field contains the ClientID value of the record that is their family. Where that simpler approach falls apart is when you have a child whose parents have split, so they are a member of 2 families. The design suggested in the article copes with that. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
From: Dennis on 28 May 2010 14:56 Allen, Thanks once again. And thank you for all of your assitanced this past year. I could not have gotten as far as I have with Access with yours and everyone else's help. Thanks again for your time and assitance. I am very grateful. Dennis -- Dennis "Allen Browne" wrote: > "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message > news:403FA6DC-66EB-413D-9780-5F0BBCE7F68E(a)microsoft.com... > > I guess it is time to re-read your article. I will do that tomorrow. > > Thanks for the input. > > Dennis, there is another option. > > If you *never* need a person to belong to more than one group, you can put a > foreign key in the same table. Enter the families and persons into the one > table, and just include a FamilyID in that table. Then for any person, that > field contains the ClientID value of the record that is their family. > > Where that simpler approach falls apart is when you have a child whose > parents have split, so they are a member of 2 families. The design suggested > in the article copes with that. > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > . >
|
Pages: 1 Prev: Column Orders Next: Filtering, RecordsetClone, Main Form - Subform Issues |