Allen Browne’s Name table design

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

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
 
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,

I guess it is time to re-read your article. I will do that tomorrow.
Thanks for the input.



Dennis
 
Dennis said:
I guess it is time to re-read your article. I will do that tomorrow.
Thanks for the input.
Click to expand...

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,

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
 
Back
Top