Multi_field Primary Key to FK

[Frank Situmorang]

Hello,

I have a one Field PK linked with 1 Field FK. My question is how can we link
a 4 field PK with a one field FK. Should we make also the FK a 4-Field
Froeign key?

Thanks for any idea

 

[tina]

comments inline.

Hello,

I have a one Field PK linked with 1 Field FK. My question is how can we link
a 4 field PK with a one field FK.

the short answer is, you can't.

Should we make also the FK a 4-Field
Froeign key?

if you intend to keep the 4-field primary key, then wherever it's used as a
foreign key, you must use those same four fields. that's exactly why i never
use a multi-field pk where that key will be used as foreign key in another
table. you might want to consider using a surrogate pk in your first table
instead, probably an autonumber, and setting a unique index on those four
fields you're now using as pk, to retain their combined unique-ness.

hth

 

[Frank Situmorang]

Thanks Tina for your quick response. The reason I intend to do it is this,

Formely I have a one field PK in my church database. I give blank database
to each churches for them to fill out their data.

When we come to consolidation of data in the higher level of
organization(Office), the Primary Key could duplicate.

The level of our office is as follows( bottom up).
1. Local church
2. Regional
3. Union
4. Devision
I already have tables of the above 4 hirarchies.

Since I want to have it always unique no matter in whci level it is
consolidated, be assiging the chruch ID, Regional ID UnionID and Division ID,
the PK is always for each member and their addresses. So we always know where
the member is now.

The problem is , when we want to have a query in order to produce a report,
each of the above officess will have a FK of the PK from the others. For
example PK in member table, will FK in church table, PK in church table will
be FK in Regional table...and so forth. The problem is since the member is
the one we must know in each of the office hrarchy, so we want to have a 4
filed primary key of member table linked to FK in chruch table. But how can
we do it.

If you have any idea Tina, we appreicate your idea how can we handle this
kind of database that can be used world wide, althouh each church will fill
out their data separately, just by knowing their ChurchID, REgional ID, Union
ID and Division ID.

Many thanks

 

[Klatuu]

Frank,
I have been watching your questions in several posts. This post shed a lot
of light on your plight.

If I understand correctly, each church will have a database and its data
will then be sent to a body above it. Assuming the tranfer will be a one
way tranfer. That is, it will always go up from Church to Region, to Union,
to Division, but never the other way, I would suggest you would find like a
bit eaiser if rather than trying to kee these records unique all the way up
through the process, you design an import scheme where the receiving entity
adds it's own indentifiers to the records.

 

[Frank Situmorang]

Thanks Klatuu for your way out. Yes it is true that the data will be reported
to the body above it. Yes the memberID which is an autonumber at the chruch
level will change when it is consolidated in the Region level. In the church
level I have a primary key of address table linked to the member table with
One to many. When it is in the REgional level( the body above the church)
when I consolidated the address ID say no. 1, it will duplicate with the
addressID of other chruch of no. 1 too. Than is now my problem Klatuu.

Whant is your suggestion if we want to consolidate the data in the higher
level of office?

Thanks in advance

 

[Klatuu]

Frank, I need to think about this for a bit. I will try to get back to you
later today, or tomorrow morning.

 

[Frank Situmorang]

Thanks Klatuu for your willingness to help me. This will be a great blessing
to our churches if I can make it. The standalone database is OK now, but when
I want to develop it to the wider scope, I have a problem with PK when
consolidated.

Maybe I need to give you more info on my query herere that can produce a
report in sorting order by Chruch, Regional, Union, and Division ( from
lowest to higher office). For your info:
1. bukuangkby is my member table
2. KbyAngAlamat is my address table
3. tblChurchname is church table
4. tblMissionname is Regional table
5. tblUnionname is Union table
6. tblDivisionname is Division table

and the last which the world level is tblGCname, although maybe not required
(because only 1 General Conference Office, but we better make it for future
development in case we need to combine with the other denomination let say
Presbytarian.

In each lower level table has the FK of the PK the higher office table in
order to enable us to make a report by level.

Here is my Query in SQL view:
SELECT tblGCname_1.GCid, tblGCname_1.GCName, tblDivisionname.DCid,
tblDivisionname.DivisionName, tblDivisionname.GC_id, tblUnionname.UCid,
tblUnionname.[Union Name], tblUnionname.DC_id, tblMissionname.MCid,
tblMissionname.[Mission Name], tblMissionname.UC_id, tblChurchname.Church_id,
tblChurchname.ChurchName, tblChurchname.MC_id, bukuangkby.Selec,
IIf([Selec]=True,Date(),Null) AS DateSelect, bukuangkby.Dear,
bukuangkby.NO_URTANGT, bukuangkby.STAT_CODE, bukuangkby.NOIN, [FNAME] & " " &
[MNAME] & " " & [LNAME] AS FullName, bukuangkby.NICKNAME, bukuangkby.FNAME,
bukuangkby.MNAME, bukuangkby.LNAME, bukuangkby.JenisKel,
bukuangkby.EmailAddress, bukuangkby.MobilPhone, bukuangkby.MobilPhone2,
bukuangkby.TGLLAHIR, bukuangkby.TEMPTLAHIR, bukuangkby.Addrs_ID,
KbyAngAlamat.HOUSEHOLDNAME, KbyAngAlamat.RADDRESS1, KbyAngAlamat.RADDRESS2,
KbyAngAlamat.RADDRESS3, KbyAngAlamat.RKODEPOS, KbyAngAlamat.No_Fax,
KbyAngAlamat.RTELP, KbyAngAlamat.RTELP2, bukuangkby.Imagepath,
bukuangkby.HLMNKEP_M, bukuangkby.TGLBPTIS_M, bukuangkby.BAPTSOLH_M,
bukuangkby.ATASSURT_M, bukuangkby.ASAL1, bukuangkby.TGL_pen,
bukuangkby.ATSPERCA_M, bukuangkby.ASAL2, bukuangkby.HLMNKEP_K,
bukuangkby.ATSSUR1_K, bukuangkby.PINDHKE_K, bukuangkby.ATSSUR2_K,
bukuangkby.KMATIAN_K, bukuangkby.KELMURT_K, bukuangkby.KETMURT_K,
bukuangkby.KELHILA_K, bukuangkby.KETHILA_K, bukuangkby.Role, bukuangkby.Note,
bukuangkby.IDGereja, *
FROM tblGCname AS tblGCname_1 RIGHT JOIN (tblDivisionname RIGHT JOIN
(tblUnionname RIGHT JOIN (tblMissionname RIGHT JOIN (tblChurchname RIGHT JOIN
(KbyAngAlamat RIGHT JOIN bukuangkby ON KbyAngAlamat.AddresID =
bukuangkby.Addrs_ID) ON tblChurchname.Church_id = bukuangkby.IDGereja) ON
tblMissionname.MCid = tblChurchname.MC_id) ON tblUnionname.UCid =
tblMissionname.UC_id) ON tblDivisionname.DCid = tblUnionname.DC_id) ON
tblGCname_1.GCid = tblDivisionname.GC_id
WHERE (((bukuangkby.NOIN)>0))
ORDER BY bukuangkby.NO_URTANGT, bukuangkby.Addrs_ID, bukuangkby.Role;

Thanks a lot in advance