address primary key

Hello,

I have 2 tables of our church membership, member table and addrfess table

In member table there is a foreign key of address table. When we come to
append this table in the higher organisation, the primary key of address will
conflict with the primary key of another church's address table. I have an
idea to keep the address primary key is always uniqueI want to make an
acronym alpha numeric. Is it posible to make an autonumber of addess table to
be alpha numeric.

Or my question how can we handle the uniqueness of the address primary key (
autonumber) so that when we come to an cosolidation of all churches member
table, the address is also can linked in the consolidation, so we still keep
track the address of all members in the Regional office of the church.

We appreciate your any idea provided.

Thanks in advance,



--
H. Frank Situmorang

On Sat, 11 Oct 2008 20:05:01 -0700, Frank Situmorang
wrote:

Hello,

I have 2 tables of our church membership, member table and addrfess table

In member table there is a foreign key of address table. When we come to
append this table in the higher organisation, the primary key of address will
conflict with the primary key of another church's address table. I have an
idea to keep the address primary key is always uniqueI want to make an
acronym alpha numeric. Is it posible to make an autonumber of addess table to
be alpha numeric.

Or my question how can we handle the uniqueness of the address primary key (
autonumber) so that when we come to an cosolidation of all churches member
table, the address is also can linked in the consolidation, so we still keep
track the address of all members in the Regional office of the church.

We appreciate your any idea provided.

Thanks in advance,

I would suggest a somewhat different approach. Add another field to the
addresses table, identifying which church this person belongs to. Make the
ChurchID and the AddressID a joint, two field primary key (and don't use
autonumber for the AddressID). This will keep the record unique even across
different churches.
--

John W. Vinson [MVP]

Thank you John for your lightening my understanding in this database design.
Can we make like an accronym name of the church in this additional field of
this address table?. I am not so clear in makeing a joint primary key to be
the primary key of the address table?. How can we make it is there any sample
of database on this special case?.

Thanks for your help.
--
H. Frank Situmorang


"John W. Vinson" wrote:

On Sat, 11 Oct 2008 20:05:01 -0700, Frank Situmorang
wrote:

Hello,

I have 2 tables of our church membership, member table and addrfess table

In member table there is a foreign key of address table. When we come to
append this table in the higher organisation, the primary key of address will
conflict with the primary key of another church's address table. I have an
idea to keep the address primary key is always uniqueI want to make an
acronym alpha numeric. Is it posible to make an autonumber of addess table to
be alpha numeric.

Or my question how can we handle the uniqueness of the address primary key (
autonumber) so that when we come to an cosolidation of all churches member
table, the address is also can linked in the consolidation, so we still keep
track the address of all members in the Regional office of the church.

We appreciate your any idea provided.

Thanks in advance,

I would suggest a somewhat different approach. Add another field to the
addresses table, identifying which church this person belongs to. Make the
ChurchID and the AddressID a joint, two field primary key (and don't use
autonumber for the AddressID). This will keep the record unique even across
different churches.
--

John W. Vinson [MVP]

On Sun, 12 Oct 2008 19:42:03 -0700, Frank Situmorang
wrote:

Thank you John for your lightening my understanding in this database design.
Can we make like an accronym name of the church in this additional field of
this address table?. I am not so clear in makeing a joint primary key to be
the primary key of the address table?. How can we make it is there any sample
of database on this special case?.

If the acronym of the church is stable and unique, yes, you can certainly use
it.

To create a two-field primary key add the church acronym field to the table,
and fill it in (use an update query if you have existing records). The field
cannot be blank if it is to be part of the key. Then open the table in design
view; ctrl-click the acronym field and the current ID field, and click the Key
icon on the toolbar.

Don't know of any sample databases that would have just this setup, but it's a
fairly common technique.
--

John W. Vinson [MVP]

Hi John,

I am not an advanced user of a database, therefore I do not understand fully
your explanation. Are you saying that we will have 2 primary key in the
address table?, One is an existing autonumber and the other one is the
Acronym joint with the autonumber?. For the acronym field in the address
table, can we make it to take the data from the acronym table, and we will
not allow to duplicate this field?. Suppose we have 100,000 churches alll
over the world, is it ok if we make 4 character/letter acronym?.

The purpose of this primary key is to keep it unique. all over the world
churches.

Thanks for your help.

--
H. Frank Situmorang


"John W. Vinson" wrote:

On Sun, 12 Oct 2008 19:42:03 -0700, Frank Situmorang
wrote:

Thank you John for your lightening my understanding in this database design.
Can we make like an accronym name of the church in this additional field of
this address table?. I am not so clear in makeing a joint primary key to be
the primary key of the address table?. How can we make it is there any sample
of database on this special case?.

If the acronym of the church is stable and unique, yes, you can certainly use
it.

To create a two-field primary key add the church acronym field to the table,
and fill it in (use an update query if you have existing records). The field
cannot be blank if it is to be part of the key. Then open the table in design
view; ctrl-click the acronym field and the current ID field, and click the Key
icon on the toolbar.

Don't know of any sample databases that would have just this setup, but it's a
fairly common technique.
--

John W. Vinson [MVP]

On Mon, 13 Oct 2008 07:14:13 -0700, Frank Situmorang
wrote:

Hi John,

I am not an advanced user of a database, therefore I do not understand fully
your explanation. Are you saying that we will have 2 primary key in the
address table?, One is an existing autonumber and the other one is the
Acronym joint with the autonumber?. For the acronym field in the address
table, can we make it to take the data from the acronym table, and we will
not allow to duplicate this field?. Suppose we have 100,000 churches alll
over the world, is it ok if we make 4 character/letter acronym?.

The purpose of this primary key is to keep it unique. all over the world
churches.

A table can have one and only one primary key - but that key can consist of
one field, two fields, or even ten fields. My suggestion is that your table's
key should consist of a church identifier (unique to the church) and a member
ID (unique within the church). An autonumber would work within each individual
church's database, but for the consolidated database the memberID should be a
Long Integer; for ease of maintenance and use I'd make it a Long Integer in
both the individual church database and the combined database. You'll need
some VBA code to implement a "Custom Counter" to assign the member ID.

I would NOT recommend a four letter church name acronym, especially if you're
talking about that many churches! There would be too many duplicates (Parma
First Church, Pittsburgh First Church - which gets PFC?), so you would need to
use arbitrary codes unrelated to the church name. I'd use instead a table of
Churches with a long integer ChurchID and the church name; in an indiviual
church, the ChurchID field would simply be a long integer with its Default
Value set to that church's ID.

--

John W. Vinson [MVP]

Thank you John for your explanation. I see in the sample of the swithboard
items table of MS Access sample there are 2 primary keys, why it is possilbe.

I am still not clear the mechanism when we come to consolidation of churhes
data in the regional office of the churches. Should we add additional primary
key of member?(custom counter)?. and this member table ( in the regional
office level) link with the unique address primarykey? of the chruch level?'

Thanks for your help. Sorry it could be my language problem too, because in
Indonesia we seldom use English, we have Indonesian language.
--
H. Frank Situmorang


"John W. Vinson" wrote:

On Mon, 13 Oct 2008 07:14:13 -0700, Frank Situmorang
wrote:

Hi John,

I am not an advanced user of a database, therefore I do not understand fully
your explanation. Are you saying that we will have 2 primary key in the
address table?, One is an existing autonumber and the other one is the
Acronym joint with the autonumber?. For the acronym field in the address
table, can we make it to take the data from the acronym table, and we will
not allow to duplicate this field?. Suppose we have 100,000 churches alll
over the world, is it ok if we make 4 character/letter acronym?.

The purpose of this primary key is to keep it unique. all over the world
churches.

A table can have one and only one primary key - but that key can consist of
one field, two fields, or even ten fields. My suggestion is that your table's
key should consist of a church identifier (unique to the church) and a member
ID (unique within the church). An autonumber would work within each individual
church's database, but for the consolidated database the memberID should be a
Long Integer; for ease of maintenance and use I'd make it a Long Integer in
both the individual church database and the combined database. You'll need
some VBA code to implement a "Custom Counter" to assign the member ID.

I would NOT recommend a four letter church name acronym, especially if you're
talking about that many churches! There would be too many duplicates (Parma
First Church, Pittsburgh First Church - which gets PFC?), so you would need to
use arbitrary codes unrelated to the church name. I'd use instead a table of
Churches with a long integer ChurchID and the church name; in an indiviual
church, the ChurchID field would simply be a long integer with its Default
Value set to that church's ID.

--

John W. Vinson [MVP]

On Mon, 13 Oct 2008 19:50:01 -0700, Frank Situmorang
wrote:

Thank you John for your explanation. I see in the sample of the swithboard
items table of MS Access sample there are 2 primary keys, why it is possilbe.

I am still not clear the mechanism when we come to consolidation of churhes
data in the regional office of the churches. Should we add additional primary
key of member?(custom counter)?. and this member table ( in the regional
office level) link with the unique address primarykey? of the chruch level?'

Thanks for your help. Sorry it could be my language problem too, because in
Indonesia we seldom use English, we have Indonesian language.

I would suggest having exactly the same design of Members table in both the
regional office and the individual church databases, just for ease of
maintenance.

The table would have a structure like:

Members
ChurchID Long Integer, Primary Key
MemberID Long Integer, Primary Key
Surname
GivenName
other biographical data as appropriate

In the table in Church #125 you would have (apparently redundant) records in
the table, ALL of them 125 in the ChurchID field. The members would be
MemberID 1, 2, 3, ..., 85 and so on; this value would be unique by itself, but
the primary key would have pairs

125;1
125;2
125;3

and so on. The combination is unique, and the 125 doesn't really play a role
*in this database*.

But in the regional database you would have records from church 125, and
church 78, and church 33. Each church's data would have its own series of
MemberID's from 1 to however many members the church has. In the regional
office database you would have records like

33; 1
33; 2
33; 3
....
33; 120
78; 1
78; 2
78; 3
....
78; 225
125; 1
125; 2
125; 3

There would be lots of Member # 1 records - but they'd all be for different
ChurchIDs; and there'll be lots of Church #78 records - but they'll all have
different MemberID's. The combination of the two fields will be unique.

For maintenance, you could do something like export all of the records from
Church 78's database to an external Text file, or dBase file; or just zip and
email the entire database. The central office could then just (say) delete all
records for ChurchID 78 and run an Append query to load the current set. You
might want to keep a historical table of former members rather than deleting,
in an archive table of some sort.

Hope this helps!
--

John W. Vinson [MVP]

Thanks very much John, this really help me, but the idea is very helpful,
especially our Seventh Day Adventist churches have the organization hirarchy
as follows starting from the lowest level:
1. Individual/local church
2. Regional Office
3. Union Office
4. Division Office
5. General Confrence ( World Office)

From what I learned from you, in the member table, we should have all the
above ID to be a primary key. Am I right?. The reporting system is bottom up.

Is it possible to make 5 fields to be a primarykey?. Now how about the
address table, should we make it like a member table? with other 5 fields to
be a primary key?, so that all unique?


--
H. Frank Situmorang


"John W. Vinson" wrote:

On Mon, 13 Oct 2008 19:50:01 -0700, Frank Situmorang
wrote:

Thank you John for your explanation. I see in the sample of the swithboard
items table of MS Access sample there are 2 primary keys, why it is possilbe.

I am still not clear the mechanism when we come to consolidation of churhes
data in the regional office of the churches. Should we add additional primary
key of member?(custom counter)?. and this member table ( in the regional
office level) link with the unique address primarykey? of the chruch level?'

Thanks for your help. Sorry it could be my language problem too, because in
Indonesia we seldom use English, we have Indonesian language.

I would suggest having exactly the same design of Members table in both the
regional office and the individual church databases, just for ease of
maintenance.

The table would have a structure like:

Members
ChurchID Long Integer, Primary Key
MemberID Long Integer, Primary Key
Surname
GivenName
other biographical data as appropriate

In the table in Church #125 you would have (apparently redundant) records in
the table, ALL of them 125 in the ChurchID field. The members would be
MemberID 1, 2, 3, ..., 85 and so on; this value would be unique by itself, but
the primary key would have pairs

125;1
125;2
125;3

and so on. The combination is unique, and the 125 doesn't really play a role
*in this database*.

But in the regional database you would have records from church 125, and
church 78, and church 33. Each church's data would have its own series of
MemberID's from 1 to however many members the church has. In the regional
office database you would have records like

33; 1
33; 2
33; 3
....
33; 120
78; 1
78; 2
78; 3
....
78; 225
125; 1
125; 2
125; 3

There would be lots of Member # 1 records - but they'd all be for different
ChurchIDs; and there'll be lots of Church #78 records - but they'll all have
different MemberID's. The combination of the two fields will be unique.

For maintenance, you could do something like export all of the records from
Church 78's database to an external Text file, or dBase file; or just zip and
email the entire database. The central office could then just (say) delete all
records for ChurchID 78 and run an Append query to load the current set. You
might want to keep a historical table of former members rather than deleting,
in an archive table of some sort.

Hope this helps!
--

John W. Vinson [MVP]

On Tue, 14 Oct 2008 02:01:01 -0700, Frank Situmorang
wrote:

Thanks very much John, this really help me, but the idea is very helpful,
especially our Seventh Day Adventist churches have the organization hirarchy
as follows starting from the lowest level:
1. Individual/local church
2. Regional Office
3. Union Office
4. Division Office
5. General Confrence ( World Office)

From what I learned from you, in the member table, we should have all the
above ID to be a primary key. Am I right?. The reporting system is bottom up.

Is it possible to make 5 fields to be a primarykey?. Now how about the
address table, should we make it like a member table? with other 5 fields to
be a primary key?, so that all unique?

Well, you don't need a variable field for level 5, since there's only one
General Conference (unless you plan to expand this database to include us
Presbyterians)!

I'd actually *not* use a four field primary key; it gets pretty hard to use
especially if you have related tables (such as group memberships, donations,
etc.) I'm not sure how much information you want to propagate "up" to the
regional, union etc. offices; it might be better to have just the ChurchID and
MemberID as a joint primary key, and have fields in the (separate) Churches
table to indicate which higher level offices that church is in.

If the Regional (union, division, world) office needs to know addresses, then
it would be sufficient to also have ChurchID and MemberID as a two field
primary key.

Have you checked with the General Conference to see if they already *have* a
member database? I would guess that they do. If not, maybe they'd be
interested; but there are enough Seventh Day Adventist churches and members
that you should really consider implementing this in SQL/Server (with an
Access frontend) rather than in a native Access database.
--

John W. Vinson [MVP]