Creating Church Directory - Need Help

  • Thread starter Thread starter Pastor John
  • Start date Start date
P

Pastor John

I am a pastor who is using Access to maintain church records. I want to
create a report either in Access or in Word through merge that would be
a directory. Each family record would look as follows:

Head of House Phone #
Spouse
Children
Address

While this seems straight forward my problem lies within my tables.

I have one table for families which store the family address, etc

I have a second relevant table for individuals which stores birthdays,
membership, attendance, etc.

For purposes of the directory I have created a field in individuals that
takes a 1 for Head of House, 2 for Spouse and 3 for children and other

I created three queries to search for one, two and three - though there
must be an easier way. This works except where multiple children are
involved - then the form shows record 1 with child 1; record 2
duplicates except for child 2, etc.

I tried a subform - but Access doesn't like using subforms with three
queries and refuses to allow me to bind the subform which makes it
useless.

Any help or ideas would be appreciated.

Pastor John
 
Hi Larry,

In family table I have a key field called familyid which is unique to
the family. In the individual table I have a key field called indid
which is unique to each individual. I then have the familyid field as a
separate field in the individual table. That is the link between the two
tables.

Pastor John
 
I am a pastor who is using Access to maintain church records. I want to
create a report either in Access or in Word through merge that would be
a directory. Each family record would look as follows:

Head of House Phone #
Spouse
Children
Address

Would you want this to look like

Jim Richards 555-5555
Janet Richards
Kevin, Lucy, Emily
333 West Maple St.

i.e. with all the children on one line?

If so you'll need a little bit of VBA code; it's hard to do in a
Query, as you've found. This question comes up often, and several
people have posted sample code; one example is at

http://www.mvps.org/access/modules/mdl0004.htm
 
John,

Thank you for your reply. Yes the directory listing is exactly as you
showed it.

I read the material on the web site and have just one question. That
material related to several fields in a database. So that for record one
I would concantenate three fields to show a common listing.

My problem is that all people are in the same field. Unless I establish
three databases one for head of house, one for spouse and one for
children or duplicate my work by adding spouse and children and head of
house fields to the family records, there is only one field to
concantenate.

The problem I am encountering is how to:

Head of Household: look up field 5 for number 1 with family record 1
then list name
Spouse: look up field 5 for number 2 with family record 1 then list name
Children: look up field 5 for number 3 with family record 1 then
concantenate all names and list
Address

Thanks - any further guidance would be appreciated.

Pastor John
 
My problem is that all people are in the same field. Unless I establish
three databases one for head of house, one for spouse and one for
children or duplicate my work by adding spouse and children and head of
house fields to the family records, there is only one field to
concantenate.

You don't need three *tables* - you can apply criteria on the query to
select just 1, 2 or 3. The list of children would be drawn from a
Query selecting only the FamilyID and the names of children by using a
criterion of 3 on the field.
 
Thanks John, I'm confused but I think it is time for me to study and try
some things.

In order to get these fields into the directory listing I suspect that I
am going to have to use the field source property to call up a specific
query. For Head it would be a sql query with 1 as source; for Spouse it
would be an sql query with 2 as source; for children it would be an sql
query with 3 as the source.

If this is correct and possible, then I need to study the concantenate
function to see how to use the function with a variable number of
children.

Thanks,

Pastor John
 
Back
Top