Prev: Conversion To Acc2007 or beyond Suggestions ?
Next: Make Query or Linked Table Read Only
From: Kathy R. on 28 Feb 2010 11:31


Dennis wrote:
> Kathy,
>
> When you produce a name and address query, which address do you use? Or do
> you have to use VBA code to obtain the current addres?

A simple query at the moment.

SELECT tblFamily.FamLastName, tblIndividual.FirstName,
tblIndividual.ContactStatus, tblAddress.Street, tblAddress.City,
tblAddress.State, tblAddress.ZipCode, tblAddress.ActiveAddress
FROM (tblFamily INNER JOIN tblIndividual ON tblFamily.FamID =
tblIndividual.InFamID) INNER JOIN tblAddress ON tblFamily.FamID =
tblAddress.AdFamID
WHERE (((tblIndividual.ContactStatus)="Primary Contact") AND
((tblAddress.ActiveAddress)=Yes));

The "ActiveAddress" field is a Yes/No field with its default value set
as yes. Most addresses are active and this way the user doesn't have to
click "Yes" every time they enter an address.

I'm still working out the kinks, and need to get the syntax right so
that I can do Mr. and Mrs. John Doe and Ms. Jane Smith (involving
prefixes and whether there is a secondary contact or not), but I don't
think that'll be a problem.

I can also take this one step further and add the tblGroup and
tblGroupMembers to the query so that I can pull up names/addresses for
just the Trustee Committee or for the newsletter mailing list, etc.

I am curious to hear from the experts though... is a query the thing to
use here, or is there some VBA code that would make the job easier? For
years I was the only person to use the database so I just changed the
query by hand each time. It would not be a pretty sight in the office
though if anything should ever happen to me. I know there's a way to
make a form for the user to choose which group they'd like to see a
report/list/mailing labels for and that is one of the things I'll be
adding to the design this time around. That will require some coding,
but the underlying query will remain the same.


> Also, I noticed that you do not have a family relationsihp field (Husband,
> wife, son, daughter). Did you find that you did not need it?


I use "Primary Contact," "Secondary Contact," "Child," "Adult Child" in
place of those. Not every adult male is a husband, not every adult
female is a wife. The primary contact would be the head of household -
a single male or female, or the husband of a married couple. The
secondary contact would be the wife. A child could be either a son or a
daughter. I can figure out which because I also have a "gender" field
(another one of the bits of information we Methodists need to track).

Each individual is a member of a family (linked on the FamID), even if
that individual is a family of one.

Thanks for your interest Dennis. The more questions and ideas I hear
the better. It makes me think about things I may not have thought of
and keeps me focused on the proper way to do things!

Kathy R.
From: Dennis on 28 Feb 2010 23:26
Kathy,

Your comment: I am curious to hear from the experts though... is a query
the thing to use here, or is there some VBA code that would make the job
easier? For years I was the only person to use the database so I just
changed the query by hand each time. It would not be a pretty sight in the
office though if anything should ever happen to me.

My Response: I'm not a expert by any means, but from all my questions in
this forum and from what I've read, queries or SQL statements in the Form's
Source property is the way to go.


Your comment: I know there's a way to make a form for the user to choose
which group they'd like to see a report/list/mailing labels for and that is
one of the things I'll be adding to the design this time around. That will
require some coding, but the underlying query will remain the same.

My Response: I've already done this, if you want I could post the code
here. There are a couple of tricks. In the On Open event of the report, you
have it open a form by using the command

strDocName = "frmMemberInfo"
DoCmd.OpenForm strDocName, , , , ,acDialog

You want to have the popup window as a dialog box, this makes it a “modal”
(sp?) window. When user had entered all of the desired parameters and hit
the Print / Process report button, don't close the pop-up window – instead
hide the pop-up window by:

Me.Visible = False

The window will no longer be visible, but you still have access to all of
the data. From what I've read and from what people on the forum suggest,
don't return the values from the pop-up via global variables, instead you can
access the variables in the pop-up window from within the calling VBA code by:

mylocalvariable = Forms!formname!controlname

mylocalvar = forms!frmMemberInfo!cboCustNo would return the value of the
control cboCustNo on the frmMemberInfo form.


Fred said he had to address the issue of couple that did not take the other
person's name – such as Mark Jones and Mary Smith. Do you have that issue,
and if so how are you address it?

Why do you have a Family Last Name on the Family table?


Also, on the data entry form, do you provide a way to search for a family
name (I assuming that is what the Family Last Name is form, but want to make
sure).

Also, can you type in a name and find to which family it is associated?

Also, I'm read through you description again and did not see where you
describe you data entry screen(s). Would you be so kind doing that? I'm
having trouble visualizing the organization of the data entry form?



Thanks

Dennis

From: Fred on 1 Mar 2010 10:08
To: Everyone

Lots of good thoughts & ideas.

I write and run db's for a lot of organizations. And the one thing that I
learned is that the design needs to be driven by the mission for your DB, and
the particulars of the mission and the organization.

There is probably ony one sentence that I'd disagree with, and that is when
Dennis said that listing a seperate family name would be a duplication of
data and thus violation of normalization. The people's names are, of course
an entity. But the family name is also an entity, which is the name that
they wish their family to be called by. Although last names would often be
duplicated, "often" doesn't meet the standard for being able to derive one
from the other.

Lastly, regarding the level of user that the DB is designed for, I do a lot
of databases designs for people who are slightly smart and who have received
about 1 hour of Access training. I teach them what tables, linked tables,
queries, form and reports are, and what they do. Also how to set criteria
in a query design grid. Of this approach is good for some situations and
terrible for others. It does cut development time down to about 1/4. Which
is good for me because I'm really weak on a lot of developer stuff (coding
etc.) :-)






"Dennis" wrote:

> Kathy,
>
> Your comment: I am curious to hear from the experts though... is a query
> the thing to use here, or is there some VBA code that would make the job
> easier? For years I was the only person to use the database so I just
> changed the query by hand each time. It would not be a pretty sight in the
> office though if anything should ever happen to me.
>
> My Response: I'm not a expert by any means, but from all my questions in
> this forum and from what I've read, queries or SQL statements in the Form's
> Source property is the way to go.
>
>
> Your comment: I know there's a way to make a form for the user to choose
> which group they'd like to see a report/list/mailing labels for and that is
> one of the things I'll be adding to the design this time around. That will
> require some coding, but the underlying query will remain the same.
>
> My Response: I've already done this, if you want I could post the code
> here. There are a couple of tricks. In the On Open event of the report, you
> have it open a form by using the command
>
> strDocName = "frmMemberInfo"
> DoCmd.OpenForm strDocName, , , , ,acDialog
>
> You want to have the popup window as a dialog box, this makes it a “modal”
> (sp?) window. When user had entered all of the desired parameters and hit
> the Print / Process report button, don't close the pop-up window – instead
> hide the pop-up window by:
>
> Me.Visible = False
>
> The window will no longer be visible, but you still have access to all of
> the data. From what I've read and from what people on the forum suggest,
> don't return the values from the pop-up via global variables, instead you can
> access the variables in the pop-up window from within the calling VBA code by:
>
> mylocalvariable = Forms!formname!controlname
>
> mylocalvar = forms!frmMemberInfo!cboCustNo would return the value of the
> control cboCustNo on the frmMemberInfo form.
>
>
> Fred said he had to address the issue of couple that did not take the other
> person's name – such as Mark Jones and Mary Smith. Do you have that issue,
> and if so how are you address it?
>
> Why do you have a Family Last Name on the Family table?
>
>
> Also, on the data entry form, do you provide a way to search for a family
> name (I assuming that is what the Family Last Name is form, but want to make
> sure).
>
> Also, can you type in a name and find to which family it is associated?
>
> Also, I'm read through you description again and did not see where you
> describe you data entry screen(s). Would you be so kind doing that? I'm
> having trouble visualizing the organization of the data entry form?
>
>
>
> Thanks
>
> Dennis
>
From: Kathy R. on 2 Mar 2010 16:33
>
> Fred said he had to address the issue of couple that did not take the other
> person's name – such as Mark Jones and Mary Smith. Do you have that issue,
> and if so how are you address it?

If I'm addressing both it would be Mr. and Mrs. Mark Jones (similar to
if it were Mark Jones III and Mary Smith, I'd address them as Mr. and
Mrs. Mark Jones III even though Mary isn't the III) Singly, It'd be Mr.
Mark Jones and Ms. Mary Smith.
>
> Why do you have a Family Last Name on the Family table?

From Fred: There is probably ony one sentence that I'd disagree with,
and that is when Dennis said that listing a seperate family name would
be a duplication of data and thus violation of normalization. The
people's names are, of course an entity. But the family name is also
an entity, which is the name that they wish their family to be called
by. Although last names would often be duplicated, "often" doesn't meet
the standard for being able to derive one from the other.

Hmm... hadn't really thought of that before. I suppose, since if I'm
using I consider the last name of the primary contact to be the "family
name," that it would be duplicate data. But as Fred says, "a family name
is also an entity." For now I'm going to leave the field as it is.
But I will think on it some more. If anyone else has an opinion, either
pro or con, and would like to share their reasoning, I'd love to hear it.


> Also, on the data entry form, do you provide a way to search for a family
> name (I assuming that is what the Family Last Name is form, but want to make
> sure).

> Also, can you type in a name and find to which family it is associated?
Do you mean type in "Bob Jones" to find out if he's Tom and Sue Jones'
son or Bill and Jane Jones' son? I haven't built that capability in.
Our membership isn't so large that we don't know the family
relationships. Or, if we don't know there's at most, only a few
"Joneses" to click on and see. I can see that this would be a problem
if the data entry person didn't know the families, especially if the
child had a different last name. The search combobox on my main family
form is restricted to primary contacts to make the list shorter, but it
could be easily adapted to include everyone and then jump to the
associated family record on that form.

> Also, I'm read through you description again and did not see where you
> describe you data entry screen(s). Would you be so kind doing that? I'm
> having trouble visualizing the organization of the data entry form?

There are two main data entry forms.

The first is to enter information about the family, home address and
basic information about the individuals in that family. This is a
"first contact" type of form. In a church setting we'll get basic
information like name and address long before we get detailed
information like birthdate.

It's a main form with two separate subforms.

The main form contains the FamLastName, FamilyNotes (a memo field), and
a combobox that I use to search for a family and jump to their record.
It displays FamLastName, FirstName (of primary contact). Easy to use
- type in a few letters, tap F4 and choose a name, or for mouse users,
just click the drop-down arrow and choose.

Subform 1 is the address information in form view, with navigation
buttons so you can add or go to a second address. Fields are street,
city, zip, homephone, address type (primary, winter, college, etc),
active address.

Subform 2 is for the basic individual's information. It is in datasheet
format and contains FirstName, NickName, LastName, Suffix, Gender,
ContactStatus (Primary, secondary, child), and Membership Status
(Member, Constituent, Newsletter Only)

--------------

The second main form is for detailed Individual information. I haven't
redesigned this one yet - my original form was created in Access 95 long
before tabbed windows were available. But I envision a main form with
The Individual information on it title, first, middle, last, nickname,
maidenname, birthdate, marriagedate, notes, occupation, work place. All
of those details we gather over time.

Subform 1 for address information (handy when you have two people with
the same name and you're trying to figure out which is which quickly).

Subform 2 for phone number info (personal phone numbers like cell or work)

Subform 3 for email info

Tabs for membership information one each for Joining, Termination,
Baptism (it's a church database), and Death. And a tab for
committees/groups. I haven't designed anything with tabs yet so it
promises to be a learning experience!


Kathy R.




From: John W. Vinson on 2 Mar 2010 20:58
On Tue, 02 Mar 2010 16:33:20 -0500, "Kathy R." <wild_rose(a)graffiti.net> wrote:

>>
>> Fred said he had to address the issue of couple that did not take the other
>> person�s name � such as Mark Jones and Mary Smith. Do you have that issue,
>> and if so how are you address it?
>
>If I'm addressing both it would be Mr. and Mrs. Mark Jones (similar to
>if it were Mark Jones III and Mary Smith, I'd address them as Mr. and
>Mrs. Mark Jones III even though Mary isn't the III) Singly, It'd be Mr.
>Mark Jones and Ms. Mary Smith.
>>
>> Why do you have a Family Last Name on the Family table?
>
> From Fred: There is probably ony one sentence that I'd disagree with,
>and that is when Dennis said that listing a seperate family name would
>be a duplication of data and thus violation of normalization. The
>people's names are, of course an entity. But the family name is also
>an entity, which is the name that they wish their family to be called
>by. Although last names would often be duplicated, "often" doesn't meet
>the standard for being able to derive one from the other.
>
>Hmm... hadn't really thought of that before. I suppose, since if I'm
>using I consider the last name of the primary contact to be the "family
>name," that it would be duplicate data. But as Fred says, "a family name
>is also an entity." For now I'm going to leave the field as it is.
>But I will think on it some more. If anyone else has an opinion, either
>pro or con, and would like to share their reasoning, I'd love to hear it.

My church database does have a family name field. For one thing, asserting
that all members of a household should have the same surname is not true in
all households! I'm John Vinson; my wife is Karen Strickler. There are two
sisters in my church, living together; both are widows, and both kept their
married names; there are lots of such anomalies. I'd much rather have the
flexibility to address a family as "The Andersons", "David & Angelina
Ramirez", "Mr. & Dr. Roberts", "Ann Jones and Mary Smith", or however *that
family* would prefer to be addressed.

--

John W. Vinson [MVP]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Conversion To Acc2007 or beyond Suggestions ?
Next: Make Query or Linked Table Read Only