4 Fields PK linked to 1 field FK

[Frank Situmorang]

Hello,

I tried to desingn a church membership database that can be used worldwide.
The blank database will be given to all churches all over the world.
Separately they will fill in their own data, but we want to maintain the
uniqueness of the member ID dan address ID, so when we come to consolidation
these 2 PK is always unique.

We have our level of organization/offices asm follows starting from the lower:
1. local church _ Chruch ID as PK
2. Regional _ REgional ID as PK
3. Union - Union ID as PK
4 Division - Division IS as PK

On the member table as well as Adress table, we want to make a 4 field PK(
derives from the ID of the above 4 PK) will be linked to 1 field FK in the
church table. The purpose of this when we consolidate it in the anyone of the
above 3 upper level, the uniqueness of member and address data are still
guaranteed.

My question is how can we do the 4 PK will go to 1 field FK. Currently I use
combo but because it is 1 field Primary Key, it is easy.

Thanks for any idea.

 

[NTC]

merging the fields into one field is easy; and can be done with your
AppendQuery when consolidating the remote tables records into the master
table; just make a calculated field in the AppendQuery that is:
FinalKey:[Church_ID]&[Regional_ID]&[etc]

however it isn't a great idea; actually it is a bad idea; particularly
when churches leave, move regions, more regions added etc. This FinalKey
becomes incorrect gradually.

you are better simply to assign a unique ID to the person at the master
level; keep the other IDs in their fields as is. It is simpler and better in
the long run.

 

[Frank Situmorang]

Thanks NTC for your response. I am not quite clear about your saying Final
key. For your info, that we have the regional table, if there is additional
region we can add it in the table.

But anyway your rationale could be right. So are you saying that database
sturcture in the regional office is not the same as in the local churches?.
What I have in mind that it is the same.

What I we plan to have, that regional office will send consolidated data to
higher office which is Union Office, and Union Office will send it's
consoldiated data to higher office which is Division Office and so forth upto
the highest office which is World Head Quesrter. And the information we want
to know is members and address.

By having the new field (new PK)in each level means the structure of the
databse is not the same and how can we combined/consolidated it.

Thanks for your idea.
--
H. Frank Situmorang

merging the fields into one field is easy; and can be done with your
AppendQuery when consolidating the remote tables records into the master
table; just make a calculated field in the AppendQuery that is:
FinalKey:[Church_ID]&[Regional_ID]&[etc]

however it isn't a great idea; actually it is a bad idea; particularly
when churches leave, move regions, more regions added etc. This FinalKey
becomes incorrect gradually.

you are better simply to assign a unique ID to the person at the master
level; keep the other IDs in their fields as is. It is simpler and better in
the long run.

Hello,

I tried to desingn a church membership database that can be used worldwide.
The blank database will be given to all churches all over the world.
Separately they will fill in their own data, but we want to maintain the
uniqueness of the member ID dan address ID, so when we come to consolidation
these 2 PK is always unique.

We have our level of organization/offices asm follows starting from the lower:
1. local church _ Chruch ID as PK
2. Regional _ REgional ID as PK
3. Union - Union ID as PK
4 Division - Division IS as PK

On the member table as well as Adress table, we want to make a 4 field PK(
derives from the ID of the above 4 PK) will be linked to 1 field FK in the
church table. The purpose of this when we consolidate it in the anyone of the
above 3 upper level, the uniqueness of member and address data are still
guaranteed.

My question is how can we do the 4 PK will go to 1 field FK. Currently I use
combo but because it is 1 field Primary Key, it is easy.

Thanks for any idea.

 

[Frank Situmorang]

Hi NTC, This is not merging a key, but this is what I want to do,

In my Household Address table, I have a primary key of 4 field kyes,
consists of
DiviionID, Union Id and Regional ID and Chruch ID.

Now I have a form to fill in the data of membertable
Name and other personal identity of the members and there is a field to be a
FK in the member table. Previouslys since it was an 1 Field key I used combo
and the record source is Autonumber of PK of Household Address table.

I got the idea from this forum that I can used multifield PK to keep it
uniques worldwide.

My question how can the 4 field PK of my address as a source row in my
membership input form using combo as well as inputting the 1 filed FK in the
member table.

Hope this will explain better about my problem.

Thanks in advance for your help.

--
H. Frank Situmorang

merging the fields into one field is easy; and can be done with your
AppendQuery when consolidating the remote tables records into the master
table; just make a calculated field in the AppendQuery that is:
FinalKey:[Church_ID]&[Regional_ID]&[etc]

however it isn't a great idea; actually it is a bad idea; particularly
when churches leave, move regions, more regions added etc. This FinalKey
becomes incorrect gradually.

you are better simply to assign a unique ID to the person at the master
level; keep the other IDs in their fields as is. It is simpler and better in
the long run.

Hello,

I tried to desingn a church membership database that can be used worldwide.
The blank database will be given to all churches all over the world.
Separately they will fill in their own data, but we want to maintain the
uniqueness of the member ID dan address ID, so when we come to consolidation
these 2 PK is always unique.

We have our level of organization/offices asm follows starting from the lower:
1. local church _ Chruch ID as PK
2. Regional _ REgional ID as PK
3. Union - Union ID as PK
4 Division - Division IS as PK

On the member table as well as Adress table, we want to make a 4 field PK(
derives from the ID of the above 4 PK) will be linked to 1 field FK in the
church table. The purpose of this when we consolidate it in the anyone of the
above 3 upper level, the uniqueness of member and address data are still
guaranteed.

My question is how can we do the 4 PK will go to 1 field FK. Currently I use
combo but because it is 1 field Primary Key, it is easy.

Thanks for any idea.

 

[NTC]

I think I do understand your approach of establishing a key or member
identity from the bottom up via the organization hierarchy.

I advise against this. A unique identity of a member should be assigned
from the top down assigned by the master db. This will assure no duplicates,
and will avoid confusion when the organization hierarchy goes thru changes.

There will be an interval of time between your passing new member into up
where you will have no final ID...that field will be blank until it is
assigned by the master db.

Fields to identify region, section, state, church, etc of course are fine
and usefull to have. These should remain separate fields and not merge. If a
region subdivides and one set of churches become into a new region it is easy
to change the regional value for those members. But don't change the member
ID. If a member changes churches - change those location fields as
appropriate but don't change the member ID.

Any keys generated by autonumbers merging data should not be driving your
design logic. They are in the background.

I hope this helps.

Hi NTC, This is not merging a key, but this is what I want to do,

In my Household Address table, I have a primary key of 4 field kyes,
consists of
DiviionID, Union Id and Regional ID and Chruch ID.

Now I have a form to fill in the data of membertable
Name and other personal identity of the members and there is a field to be a
FK in the member table. Previouslys since it was an 1 Field key I used combo
and the record source is Autonumber of PK of Household Address table.

I got the idea from this forum that I can used multifield PK to keep it
uniques worldwide.

My question how can the 4 field PK of my address as a source row in my
membership input form using combo as well as inputting the 1 filed FK in the
member table.

Hope this will explain better about my problem.

Thanks in advance for your help.

--
H. Frank Situmorang

merging the fields into one field is easy; and can be done with your
AppendQuery when consolidating the remote tables records into the master
table; just make a calculated field in the AppendQuery that is:
FinalKey:[Church_ID]&[Regional_ID]&[etc]

however it isn't a great idea; actually it is a bad idea; particularly
when churches leave, move regions, more regions added etc. This FinalKey
becomes incorrect gradually.

you are better simply to assign a unique ID to the person at the master
level; keep the other IDs in their fields as is. It is simpler and better in
the long run.

Hello,

I tried to desingn a church membership database that can be used worldwide.
The blank database will be given to all churches all over the world.
Separately they will fill in their own data, but we want to maintain the
uniqueness of the member ID dan address ID, so when we come to consolidation
these 2 PK is always unique.

We have our level of organization/offices asm follows starting from the lower:
1. local church _ Chruch ID as PK
2. Regional _ REgional ID as PK
3. Union - Union ID as PK
4 Division - Division IS as PK

On the member table as well as Adress table, we want to make a 4 field PK(
derives from the ID of the above 4 PK) will be linked to 1 field FK in the
church table. The purpose of this when we consolidate it in the anyone of the
above 3 upper level, the uniqueness of member and address data are still
guaranteed.

My question is how can we do the 4 PK will go to 1 field FK. Currently I use
combo but because it is 1 field Primary Key, it is easy.

Thanks for any idea.

 

[Michael Gramelspacher]

Hello,

I tried to desingn a church membership database that can be used worldwide.
The blank database will be given to all churches all over the world.
Separately they will fill in their own data, but we want to maintain the
uniqueness of the member ID dan address ID, so when we come to consolidation
these 2 PK is always unique.

We have our level of organization/offices asm follows starting from the lower:
1. local church _ Chruch ID as PK
2. Regional _ REgional ID as PK
3. Union - Union ID as PK
4 Division - Division IS as PK

On the member table as well as Adress table, we want to make a 4 field PK(
derives from the ID of the above 4 PK) will be linked to 1 field FK in the
church table. The purpose of this when we consolidate it in the anyone of the
above 3 upper level, the uniqueness of member and address data are still
guaranteed.

My question is how can we do the 4 PK will go to 1 field FK. Currently I use
combo but because it is 1 field Primary Key, it is easy.

Thanks for any idea.

Asume you have a tables

CREATE TABLE Churches
(ChurchID INTEGER NOT NULL Primary Key,
RegionID INTEGER NOT NULL,
UnionID INTEGER NOT NULL,
DivisionID INTEGER NOT NULL);

CREATE TABLE Members
(MemberID INTEGER NOT NULL,
ChurchID INTEGER NOT NULL,
RegionID INTEGER NOT NULL,
UnionID INTEGER NOT NULL,
DivisionID INTEGER NOT NULL,
CONSTRAINT fk_Churches
FOREIGN KEY (ChurchID, RegionID, UnionID,DivisionID)
REFERENCES Churches (ChurchID, RegionID, UnionID, DivisionID)
ON UPDATE CASCADE,
PRIMARY KEY (MemberID, ChurchID,RegionID,UnionID,DivisionID));

CREATE TABLE MemberAddresses
(MemberID INTEGER NOT NULL,
ChurchID INTEGER NOT NULL,
RegionID INTEGER NOT NULL,
UnionID INTEGER NOT NULL,
DivisionID INTEGER NOT NULL,
CONSTRAINT fk_Members
FOREIGN KEY (MemberID,ChurchID, RegionID, UnionID, DivisionID)
REFERENCES Churches (MemberID, ChurchID, RegionID, UnionID, DivisionID)
ON UPDATE CASCADE,
PRIMARY KEY (MemberID, ChurchID,RegionID,UnionID, DivisionID));

This only allows one address per Member.

A combobox on a form would have multiple columns. When you select the church, for example, the
afterupdate event code would insert the corresponding column values for RegionID and DivisionID and
UnionID into text boxes or comboboxes. These would be locked as they only get their values from the
event code in the church combobox.

At the church level only the MemberID is needed to identify a member. Since the structure of the
Members table will be the same at all levels, you need the multiple column key at every level.

 

[Frank Situmorang]

Thanks Michael for your suggestion. Are you saying that you agree with the 4
field PK?, what do you mean by Constraint-fk churches and What is REFERENCE
for.

Can you help me how can we do it?, Initially I have designed it for only my
church and it works, we know the member of the family per household. The
problem become complex now, when I want to desingn it to be used by every
church of our SDA church which have organizational structure as I mentioned
above.

Are you saying here that tables are using surrogate key for primary key?

Thanks in advance

 

[Michael Gramelspacher]

Thanks Michael for your suggestion. Are you saying that you agree with the 4
field PK?, what do you mean by Constraint-fk churches and What is REFERENCE
for.

Can you help me how can we do it?, Initially I have designed it for only my
church and it works, we know the member of the family per household. The
problem become complex now, when I want to desingn it to be used by every
church of our SDA church which have organizational structure as I mentioned
above.

Are you saying here that tables are using surrogate key for primary key?

Thanks in advance

Best would be if the churchID is a number assigned by the highest level. If that were the case,
then each church throughout the organization would have a unique churchID number. If that were the
case, then ChurchID and MemberID together would uniquely identify any person in you organization. I
think this was explained well by others early on. So, what is it, is a church number unique
throughout the organization?

What I provided was Data Definition Language (DDL). This is a formal way of describing and defining
tables, and is used within the database world. In this case everything can be done in the Access
table design interface.

 

[Frank Situmorang]

Thanks Michael for your idea. The problem is that chruch id is not assigned
by highest level. Yes we know the name of the Divisions, Unions, REgionals
and the churches.

Just by adding the data by themseves, it will automatically have Utonumber
Primary key of each level. And will let them choose those ID to have Final
Unique AddressID, so that can be reconqnized when consolidated in each level.
This is my intention.

On the member table we have the FK which takes data ( using combo) from the
4 field PK. I think we let the MemberID remain a surrogate key and will
change later in the consolidation.

Thanks for your next idea.
Greeting from Jakarta.

 

[Michael Gramelspacher]

Thanks Michael for your idea. The problem is that chruch id is not assigned
by highest level. Yes we know the name of the Divisions, Unions, REgionals
and the churches.

Just by adding the data by themseves, it will automatically have Utonumber
Primary key of each level. And will let them choose those ID to have Final
Unique AddressID, so that can be reconqnized when consolidated in each level.
This is my intention.

On the member table we have the FK which takes data ( using combo) from the
4 field PK. I think we let the MemberID remain a surrogate key and will
change later in the consolidation.

Thanks for your next idea.
Greeting from Jakarta.

It seems that you can have every church with a MemberID = 1, for example. How are the ChurchID
assigned? I sure hope you cannot have two churches with ChurchID = 1 in the same Region.

It looks like you will a four column key. If you create a surrogate key for the church table, I am
afraid that multiple churches will be using the same surrogate key unless the church table
originates at the highest level.