A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Church Database



 
 
Thread Tools Display Modes
  #11  
Old February 28th, 2010, 12:26 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Church Database


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?

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?



Dennis
  #12  
Old February 28th, 2010, 05:31 PM posted to microsoft.public.access.tablesdbdesign
Kathy R.[_3_]
external usenet poster
 
Posts: 25
Default Church Database



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.
  #13  
Old March 1st, 2010, 05:26 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Church Database

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

  #14  
Old March 1st, 2010, 04:08 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Church Database

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

  #15  
Old March 2nd, 2010, 10:33 PM posted to microsoft.public.access.tablesdbdesign
Kathy R.
external usenet poster
 
Posts: 20
Default Church Database


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.




  #16  
Old March 3rd, 2010, 02:58 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Church Database

On Tue, 02 Mar 2010 16:33:20 -0500, "Kathy R." wrote:


Fred said he had to address the issue of couple that did not take the other
persons 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]
  #17  
Old March 3rd, 2010, 04:40 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Church Database

Kathy, Fred, John,



"Why do you have a Family Last Name on the Family table? 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"


Ah, I see you point and agree. Thanks for the education! I love to learn
and you folks have made me think about things that I've not had to think
about before. I learn a little more each day.



Kathy,

Good luck with your database.


Thanks,
  #18  
Old March 3rd, 2010, 02:37 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Church Database

Hello Kathy,

Some answers get driven by design principles that are good to always
follow, some get driven by (those with) practical experience, but your
particular needs should also drive answers......in the end what's "right" is
what works well for you.

In general, the more thorough/powerful table structures accomodate a wider
range of scenerios in a fully databased fashion(vs. stuffing exceptions into
notes fields etc.) , but they are usually are also more complicated and more
work. Having separate tables for families and individuals is the most
powerful/versatile of the discussed structures (short of/ not counting Allen
Browne's more powerful and abscract structure only briefly mentioned) But
"powerful/versatile" can be a bad idea for a particular situation if it is
overkill.


The one thing I question structurally in your description is a subform for
addresses. This also implies a separate table. IMHO addresses are "one to
one" information for one of the other tables, and thus should probably be in
the table and form with that other entity.

Good luck!

Fred





  #19  
Old March 3rd, 2010, 11:55 PM posted to microsoft.public.access.tablesdbdesign
Kathy R.[_3_]
external usenet poster
 
Posts: 25
Default Church Database

Fred wrote:
The one thing I question structurally in your description is a subform for
addresses. This also implies a separate table. IMHO addresses are "one to
one" information for one of the other tables, and thus should probably be in
the table and form with that other entity.



Hi Fred,

Normally I would agree with you about the addresses. I do, indeed, have
a separate table for addresses. Within our congregation we have close
to a dozen families that head south for the winter (2-3 months at a
time) to permanent addresses there. I have to change their addresses
each winter and spring. In addition, we have another half dozen or more
college students. They have both home and college addresses and would
also need to be changed twice a year. Plus, the church is in a college
town and we have students from the college that attend that we keep
track of - again a home and college address.

For this reason I've put the addresses in a separate table. This way I
can just check which is active instead of changing the address back and
forth each year. I debated with myself about this and finally decided
that, once I have the tables/forms/reports setup, it would be easier to
just check a yes/no box than change the address each time.

Thank you for your comments. I really appreciate them!

Kathy R.
PS I won the debate, by the way. ;-)
  #20  
Old March 4th, 2010, 02:18 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Church Database

Kathy,

Of course you are absolutely right.

Sincerely,

Fred


PS: What was the debate?
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.