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" <(E-Mail Removed)> wrote in message
news:4AC24945-3C95-47DF-8EF8-(E-Mail Removed)...
> 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
>
>
>
Similar Threads
