Approach for multi level organizations

Hello,

I have created for our local churches a membership database. My question is
what is our approach to make a membership database for the following level of
our organization (bottom up):

1. Local churches
2. Mission/Conference ( consists of local churches)
3. Union ( consists of Missions)
4. Divison ( Consists of Unions)
5. General Conference (World level) consists of divisions

How can I make a database in the Mission level to be able to import data
from local churches ( note that church database consists of many tables)

The same routine import job is true for the other higher organization,
Union, Division and General Conference

What I have in mind, correct me if I am wrong, in the member table, there
should be a foreign key for Mission table...

Thanks for any idea provided.

--
H. Frank Situmorang

One approach is to use the tables as you have them, so each level is a
member of the next one up, and you can trace them.

An alternative approach is to put all the persons and corporate entities in
the one table of "clients." You can then define the connections between the
various clients, e.g. many people belong to a local church, and so on. This
approach has some advantages and some disadvantages.

A major advantage is that you have only one table to search to find
anything. Similarly, if you have to track things (like who paid for what),
you can just use a foreign key to the client table. This makes it really
flexible, and so I constantly lean towards this design.

The disadvantage is that it potentially makes things too flexible, and it is
possible to enter data that doesn't make sense. For example, you could
finish up with a division that is a member of a local church (which makes no
sense), so you have to take action to prevent this.

For a downloadable example of this kind of structure, see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Situmorang" wrote in message
...
Hello,

I have created for our local churches a membership database. My question
is
what is our approach to make a membership database for the following level
of
our organization (bottom up):

1. Local churches
2. Mission/Conference ( consists of local churches)
3. Union ( consists of Missions)
4. Divison ( Consists of Unions)
5. General Conference (World level) consists of divisions

How can I make a database in the Mission level to be able to import data
from local churches ( note that church database consists of many tables)

The same routine import job is true for the other higher organization,
Union, Division and General Conference

What I have in mind, correct me if I am wrong, in the member table, there
should be a foreign key for Mission table...

Thanks for any idea provided.

--
H. Frank Situmorang

Allen,

Thanks for your input, but mine is maybe not too complex. A member can not
belong to the next 2 or 3 upper level.

A member can belong to a local church, then a church can belong to a mission
then a mission belongs to a Union and a Union can belong to a Division and a
Devision belongs to a General Conference ( World Head Office).

I think what I want to try to make is upto Union. What I plan to make is

1. local church table
2. Mission table
3. Union table.

The table structure is
1. Primary key of local church - Foreign key in members table
2. Primary key of a mission table - foreign key in local church table
3. Primary key in a Union table - foreign key in mission table

my question is, in the mission table office, how can we import the data and
make a consolidation from all local churces

The same is true for office of Union table.


Thanks very much for any help
--
H. Frank Situmorang


"Allen Browne" wrote:

One approach is to use the tables as you have them, so each level is a
member of the next one up, and you can trace them.

An alternative approach is to put all the persons and corporate entities in
the one table of "clients." You can then define the connections between the
various clients, e.g. many people belong to a local church, and so on. This
approach has some advantages and some disadvantages.

A major advantage is that you have only one table to search to find
anything. Similarly, if you have to track things (like who paid for what),
you can just use a foreign key to the client table. This makes it really
flexible, and so I constantly lean towards this design.

The disadvantage is that it potentially makes things too flexible, and it is
possible to enter data that doesn't make sense. For example, you could
finish up with a division that is a member of a local church (which makes no
sense), so you have to take action to prevent this.

For a downloadable example of this kind of structure, see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Situmorang" wrote in message
...
Hello,

I have created for our local churches a membership database. My question
is
what is our approach to make a membership database for the following level
of
our organization (bottom up):

1. Local churches
2. Mission/Conference ( consists of local churches)
3. Union ( consists of Missions)
4. Divison ( Consists of Unions)
5. General Conference (World level) consists of divisions

How can I make a database in the Mission level to be able to import data
from local churches ( note that church database consists of many tables)

The same routine import job is true for the other higher organization,
Union, Division and General Conference

What I have in mind, correct me if I am wrong, in the member table, there
should be a foreign key for Mission table...

Thanks for any idea provided.

--
H. Frank Situmorang

How can you import the data from the various organisations? That's a big
question.

The anwswer will depend on how many factors, such as:
- How the levels of your organisation provide their data to you
(Excel? Comma-Separated-Values? Other?)

- How valid that data is when it arrives (relationally correct, required
fields all present, data types correct, etc.)

- Your mechanism for identifying the foreign key for the parent of the data
being organised.

Typically, it will involve importing into a temporary table, running all the
validation tests, getting the user to sort out any critical problems with
the import data and giving warnings on unlikely data, getting input from the
user for other info you need, inserting new values into lookup tables,
appending new records to existing tables, and deciding how/when to update
existing records or to just ignore any differences between existing records
and existing data in the import file.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Situmorang" wrote in message
...

I think what I want to try to make is upto Union. What I plan to make is

1. local church table
2. Mission table
3. Union table.

The table structure is
1. Primary key of local church - Foreign key in members table
2. Primary key of a mission table - foreign key in local church table
3. Primary key in a Union table - foreign key in mission table

my question is, in the mission table office, how can we import the data
and
make a consolidation from all local churces

The same is true for office of Union table.

Allen,

How can we import data from the various organization is one of my question
to you, but what I have in mind is local churches will send their softcopy
of their Access data to the regional mission, and the mission office will
then import it into it's database. Then mission officess will send their
databases to Union office then in the Union office lelvel will consolidate it
in order to know how many members all together in the Union Area. Like in
Indonesia, we have Wes Indonesia Union and East Indoensia Union.

I know this is not an easy job, I think but who know I can design it because
for Him is nothing imposible.

So what is is your suggestion. Make a data export procedures in local
churches, then they send it to higher level of organization?. I propese the
data format is access data, not an excel

Fior your info. my local church membership database will be used in all
over the Mission area....,

Thanks for your idea


--
H. Frank Situmorang


"Allen Browne" wrote:

How can you import the data from the various organisations? That's a big
question.

The anwswer will depend on how many factors, such as:
- How the levels of your organisation provide their data to you
(Excel? Comma-Separated-Values? Other?)

- How valid that data is when it arrives (relationally correct, required
fields all present, data types correct, etc.)

- Your mechanism for identifying the foreign key for the parent of the data
being organised.

Typically, it will involve importing into a temporary table, running all the
validation tests, getting the user to sort out any critical problems with
the import data and giving warnings on unlikely data, getting input from the
user for other info you need, inserting new values into lookup tables,
appending new records to existing tables, and deciding how/when to update
existing records or to just ignore any differences between existing records
and existing data in the import file.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Situmorang" wrote in message
...

I think what I want to try to make is upto Union. What I plan to make is

1. local church table
2. Mission table
3. Union table.

The table structure is
1. Primary key of local church - Foreign key in members table
2. Primary key of a mission table - foreign key in local church table
3. Primary key in a Union table - foreign key in mission table

my question is, in the mission table office, how can we import the data
and
make a consolidation from all local churces

The same is true for office of Union table.

You could write code to CreateDatabase, and export the Access tables, but
I'm not sure that's necessary. It might be easier to TransferSpreadsheet so
it exports to Excel, and then use TransferSpreadsheet again to import it
into the database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Situmorang" wrote in message
...
Allen,

How can we import data from the various organization is one of my question
to you, but what I have in mind is local churches will send their
softcopy
of their Access data to the regional mission, and the mission office will
then import it into it's database. Then mission officess will send their
databases to Union office then in the Union office lelvel will consolidate
it
in order to know how many members all together in the Union Area. Like in
Indonesia, we have Wes Indonesia Union and East Indoensia Union.

I know this is not an easy job, I think but who know I can design it
because
for Him is nothing imposible.

So what is is your suggestion. Make a data export procedures in local
churches, then they send it to higher level of organization?. I propese
the
data format is access data, not an excel

Fior your info. my local church membership database will be used in all
over the Mission area....,

Thanks for your idea


--
H. Frank Situmorang


"Allen Browne" wrote:

How can you import the data from the various organisations? That's a big
question.

The anwswer will depend on how many factors, such as:
- How the levels of your organisation provide their data to you
(Excel? Comma-Separated-Values? Other?)

- How valid that data is when it arrives (relationally correct, required
fields all present, data types correct, etc.)

- Your mechanism for identifying the foreign key for the parent of the
data
being organised.

Typically, it will involve importing into a temporary table, running all
the
validation tests, getting the user to sort out any critical problems with
the import data and giving warnings on unlikely data, getting input from
the
user for other info you need, inserting new values into lookup tables,
appending new records to existing tables, and deciding how/when to update
existing records or to just ignore any differences between existing
records
and existing data in the import file.

"Frank Situmorang" wrote in message
...

I think what I want to try to make is upto Union. What I plan to make
is

1. local church table
2. Mission table
3. Union table.

The table structure is
1. Primary key of local church - Foreign key in members table
2. Primary key of a mission table - foreign key in local church table
3. Primary key in a Union table - foreign key in mission table

my question is, in the mission table office, how can we import the data
and
make a consolidation from all local churces

Thanks Allen, I will try your seggestion

--
H. Frank Situmorang


"Allen Browne" wrote:

You could write code to CreateDatabase, and export the Access tables, but
I'm not sure that's necessary. It might be easier to TransferSpreadsheet so
it exports to Excel, and then use TransferSpreadsheet again to import it
into the database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Situmorang" wrote in message
...
Allen,

How can we import data from the various organization is one of my question
to you, but what I have in mind is local churches will send their
softcopy
of their Access data to the regional mission, and the mission office will
then import it into it's database. Then mission officess will send their
databases to Union office then in the Union office lelvel will consolidate
it
in order to know how many members all together in the Union Area. Like in
Indonesia, we have Wes Indonesia Union and East Indoensia Union.

I know this is not an easy job, I think but who know I can design it
because
for Him is nothing imposible.

So what is is your suggestion. Make a data export procedures in local
churches, then they send it to higher level of organization?. I propese
the
data format is access data, not an excel

Fior your info. my local church membership database will be used in all
over the Mission area....,

Thanks for your idea


--
H. Frank Situmorang


"Allen Browne" wrote:

How can you import the data from the various organisations? That's a big
question.

The anwswer will depend on how many factors, such as:
- How the levels of your organisation provide their data to you
(Excel? Comma-Separated-Values? Other?)

- How valid that data is when it arrives (relationally correct, required
fields all present, data types correct, etc.)

- Your mechanism for identifying the foreign key for the parent of the
data
being organised.

Typically, it will involve importing into a temporary table, running all
the
validation tests, getting the user to sort out any critical problems with
the import data and giving warnings on unlikely data, getting input from
the
user for other info you need, inserting new values into lookup tables,
appending new records to existing tables, and deciding how/when to update
existing records or to just ignore any differences between existing
records
and existing data in the import file.

"Frank Situmorang" wrote in message
...

I think what I want to try to make is upto Union. What I plan to make
is

1. local church table
2. Mission table
3. Union table.

The table structure is
1. Primary key of local church - Foreign key in members table
2. Primary key of a mission table - foreign key in local church table
3. Primary key in a Union table - foreign key in mission table

my question is, in the mission table office, how can we import the data
and
make a consolidation from all local churces