2-field prmary key into Foreignkey

[Frank Situmorang]

Hello,

I have the church membership database with
1. household address table
2. membership table

Previously I have Houlsehold address table linked to membership table with
one to many. PK of Household address is an autonumber

To maintain the uniqueness of this primary key when the data is consolidated
in the higher level of organizationj, I changed the household address table
into 2 field PK which is ChurchID (numbe,r long integer) and Autonumber.

In my member form I have the combo box to choose the address, my question is
how can we have 2 field PK be posted to 1 Field FK in the membership table.
Is it possible?

Thanks for any idea

 

[boblarson]

Why would you have a composite key using an autonumber? An autonumber in and
of itself is guaranteed to be unique and therefore is a good primary key all
by itself. My advice - get rid of the composite key.
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[Frank Situmorang]

Bob,

Here is the rationale,

lets say church A has the membeer whose address when he started inputting
the address table, is number 1

The same church database with blank data, given to church B, when he started
inputting his memberwith address, the address started number is also 1.

All those 2 churches sendt their data to the regional office and to be
combined/consolidated in the regional office/head office. for The household
address table, the number 1 in church A will conflict with mumber 1 in church
B.

That is why, when I asked this problem in this forum, they suggested me to
have a composite key. And the key is always unique.

I appreciate to have your idea again.

 

[jacksonmacd]

Autonumbers have a property which specifies whether they are
sequential or random. By changing them to Random, the chances of
having a collision between the two instances of your database is
reduced to a very small number. This would allow you to revert to a
single-field FK.

Alternatively, you maintain the two-field FK in the membership table.