Newbie needs help with a Clergy database

[SeeAll]

Hi,
To collate my research notes - at the moment the data is in an Excel
spreadsheet which is getting very large and difficult to manage - I need to
create a database of clergy, the churches where they preached and the office
they held.

I have the following data:

CLERGY DETAILS
Surname
Forename
Born when and where
Married when and where
Died when and where
Matriculation details
Ordination details

CHURCH DETAILS
Church name
Church location
Church GPS
Church photo

OFFICE HELD DETAILS
Office held - curate, vicar, rector, etc.
Start date
Finish date
Citation - where information came from

The clergyman can be in various positions throughout his/ her career at one
or many churches, i.e., curate at one church, leaves for curacy at another
church, leaves to become a vicar at another church, or may return to the
first church as vicar.

The output I need at the moment is:
Clergyman
when and where served - by year and office

Church
who, when and what position held - by year and by office held

I am sure this is very simple to do but it is beyond my capabilities,
without guidance, any help with what to put into each table and the
relationships between tables would be appreciated.

Regards

 

[KARL DEWEY]

You need to add to your table structure.
CLERGY ---
ClergyID - Autonumber - primary key
Surname
Forename
MI
Birthdate - DateTime
BirthPlace - text
Married - DateTime
MarriedPlace - text
Died - DateTime
DiedPlace - text
Matriculation - memo
Ordination - memo

CHURCH ---
ChurchID -Autonumber - primary key
Name - text
Location - text
GPS - text
Photo -

OFFICE ---
ClergyID - number - integer - foreign key
ChurchID - number - integer - foreign key
Office - text - curate, vicar, rector, etc.
Start - DateTime
Finish - DateTime
Citation - memo - where information came from

Set a one-to-many relationship between Clergy & Office and Church & Office.
Use a form/subform for Clergy/Office selecting Church in subform from
listbox.

With this structure you can build your queries to show all the churches and
positions a clergy has served.

 

[Ken Sheridan]

In addition to the model proposed by Karl you should also have an Offices
table with one row per Office; Vicar, Curate etc. This allows you to enforce
referential integrity in the relationship between Offices and OfficesHeld
(Karl's third table). Otherwise the same office could be entered
inconsistently in OfficesHeld, as a result of a simple typo for instance.

If you want to structure the matriculation details then you'll need a table
for this rather than a simple memo field in the Clergy table. This might in
turn reference another table of Institutions for instance. Don't be tempted
to structure the data within the memo field; it makes querying on this data
near impossible.

I believe CofE priests cannot marry a divorcee, or at least stay in office
if they do, but I assume they can if a spouse of one or the other has died,
so if you want to record all but the incumbent's latest marriage you'll need
to have the marriage details in a separate table with one row per marriage,
relating this to the Clergy table by a foreign key ClergyID column which
references the primary key ClerrgyID of the Clergy table (don't use names as
keys; they can be duplicated). If you are recording the incumbent's spouse's
details an interesting point would arise in the case of my wife's cousin and
his wife as they are both CofE priests! Theoretically this could be modelled
by a self referencing table (rather like the usual way of referencing another
row in an employees table for each employee's line manager), but as most
clergy are not married to other clergy you'd then need to have a People table
with the Clergy table as a sub-type of it, so you could reference one row in
People from another one. I'd imagine this is not a very common situation,
however, and its probably not worth going to those lengths.

You'd also need the usual referenced tables for parishes and other location
data. You'll find an example of ways of handling location data of this sort
at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


though it uses the civil rather than ecclesiastical parishes in my area.
The principles are the same, however.

Ken Sheridan
Stafford, England