query with multiple criteria

[baxter101 via AccessMonster.com]

I am trying to create queries to sort church membership data according to any
number of ministry groups (out of a possible 56) they volunteer for. My goal
is to create a report for each of the Ministry's with name and contact
information. I have some questions with corresponding database description
information that will hopefully enable an expert to point me in the right
direction...
Questions:
(1) I am at the query step and am looking for a way to link up the ministry
with the corresponding FName. I have entered a ministry name in the criteria
cells (e.g., "Choir") for all 28 cells in the design grid and this works in
that it only brings up those records that have "Choir: in any of the Ministry
fields, but does not really link up the FName with the ministry. For example,
FName1 could be in Choir and Fname3 could be in Sunday School. So I need
directions to get to a point with FNames and associated Ministries.
Background:
Each record contains first and last name fields for up to 7 family members (e.
g., FName1...FName7). Also, each record contains fields for up to 4
ministries for each of the 7 family members (e.g., Ministry1A...Ministry7D).
Thanks in advance for your time and assistance!
Brandye

 

[John W. Vinson]

I am trying to create queries to sort church membership data according to any
number of ministry groups (out of a possible 56) they volunteer for. My goal
is to create a report for each of the Ministry's with name and contact
information. I have some questions with corresponding database description
information that will hopefully enable an expert to point me in the right
direction...
Questions:
(1) I am at the query step and am looking for a way to link up the ministry
with the corresponding FName. I have entered a ministry name in the criteria
cells (e.g., "Choir") for all 28 cells in the design grid and this works in
that it only brings up those records that have "Choir: in any of the Ministry
fields, but does not really link up the FName with the ministry. For example,
FName1 could be in Choir and Fname3 could be in Sunday School. So I need
directions to get to a point with FNames and associated Ministries.
Background:
Each record contains first and last name fields for up to 7 family members (e.
g., FName1...FName7). Also, each record contains fields for up to 4
ministries for each of the 7 family members (e.g., Ministry1A...Ministry7D).
Thanks in advance for your time and assistance!
Brandye

Well...

DON'T do it that way.

You're "committing spreadsheet", a venial sin punishable by being required to
read about normalization.

A better design uses FOUR tables:

Families
FamilyID <Primary Key>
FamilyName < e.g. "The Jensens", "Bill and Linda Stewart", ...>
Address
<other contact information>

Members
MemberID <primary key>
FamilyID
LastName
FirstName
<other biographical data>

Ministries
MinistryID <Primary Key>
Ministry <text description>

Callings
MemberID <link to Members>
MinistryID <link to Ministries>
<other fields about this person's involvement with this ministry>

If you would like a free copy of just such a database I've set up for church
membership, send me an email at jvinson <at> wysard of info <dot> com - edit
out the blanks and fix the punctuation.

John W. Vinson [MVP]

 

[baxter101 via AccessMonster.com]

Yikes! I'd rather avoid the punishment. Hmm, this is not a database I created.
.. I was trying to work with what the church has set up and quickly show them
how to create reports for their ministry lists. Sounds like I will need to
restructure the database into 4 tables, which is going to cause some errors
in the data entry form if I don't make corresponding changes to the the
control source properties, right? Do I just type the new table name for the
fields that are moved from the 'spreadsheet' into new tables in the control
source box? That seems easy enough. Can you think of any other 'hidden'
changes that I will need to make on the data entry form? Oh also, what kind
of links do I create between the tables?
Thank you so much for the information/advice. I will take you up on the offer
for the sample database as well. I might be able to answer some of these
questions by looking at this database structure.
Brandye

I am trying to create queries to sort church membership data according to any
number of ministry groups (out of a possible 56) they volunteer for. My goal

[quoted text clipped - 16 lines]

Thanks in advance for your time and assistance!
Brandye

Well...

DON'T do it that way.

You're "committing spreadsheet", a venial sin punishable by being required to
read about normalization.

A better design uses FOUR tables:

Families
FamilyID <Primary Key>
FamilyName < e.g. "The Jensens", "Bill and Linda Stewart", ...>
Address
<other contact information>

Members
MemberID <primary key>
FamilyID
LastName
FirstName
<other biographical data>

Ministries
MinistryID <Primary Key>
Ministry <text description>

Callings
MemberID <link to Members>
MinistryID <link to Ministries>
<other fields about this person's involvement with this ministry>

If you would like a free copy of just such a database I've set up for church
membership, send me an email at jvinson <at> wysard of info <dot> com - edit
out the blanks and fix the punctuation.

John W. Vinson [MVP]

 

[John W. Vinson]

Yikes! I'd rather avoid the punishment. Hmm, this is not a database I created.
. I was trying to work with what the church has set up and quickly show them
how to create reports for their ministry lists. Sounds like I will need to
restructure the database into 4 tables, which is going to cause some errors
in the data entry form if I don't make corresponding changes to the the
control source properties, right? Do I just type the new table name for the
fields that are moved from the 'spreadsheet' into new tables in the control
source box? That seems easy enough. Can you think of any other 'hidden'
changes that I will need to make on the data entry form? Oh also, what kind
of links do I create between the tables?

Well... unfortunately, it won't be that easy. The "spreadsheet" form will have
to be discarded and replaced with a completely different form, since the
structure of the underlying tables are completely different.

You're really going to need to bite the bullet though. your analysis of the
problems with searching the "wide-flat" table for Ministries is spot on -
without MANY complex queries, it's going to be all but impossible to link an
individual person up with their individual ministries, since the table has
them all jumbled together.

Thank you so much for the information/advice. I will take you up on the offer
for the sample database as well. I might be able to answer some of these
questions by looking at this database structure.

Sent. Hope it's useful!

John W. Vinson [MVP]

 

[baxter101 via AccessMonster.com]

Thank you soooo very much. I have reviewd the database structure and all of
the snazzy buttons and whistles and am sure the church staff would love to be
able to utilize such a tool. I will offer to translate their data to this
structure, but I think they just had their data entry form created, etc., so
I'm not sure how enthusiastic they will be. However, given their main use
will be to create mailing labels for various ministries and a membership
directory, it is in their best interest. Thanks again for your valuable
expertise and speedy assistance. I will email you their information directly
if they utilize your database.
Brandye

Yikes! I'd rather avoid the punishment. Hmm, this is not a database I created.
. I was trying to work with what the church has set up and quickly show them

[quoted text clipped - 6 lines]

changes that I will need to make on the data entry form? Oh also, what kind
of links do I create between the tables?

Well... unfortunately, it won't be that easy. The "spreadsheet" form will have
to be discarded and replaced with a completely different form, since the
structure of the underlying tables are completely different.

You're really going to need to bite the bullet though. your analysis of the
problems with searching the "wide-flat" table for Ministries is spot on -
without MANY complex queries, it's going to be all but impossible to link an
individual person up with their individual ministries, since the table has
them all jumbled together.

Thank you so much for the information/advice. I will take you up on the offer
for the sample database as well. I might be able to answer some of these
questions by looking at this database structure.

Sent. Hope it's useful!

John W. Vinson [MVP]