Pimary key when consolidated

Hello,

We have the membership database in many churches. The database has member id
a primary key with the autonumber and addressID linke to the membertable and
on the report we can see member with their address.

My question is:

1. In the regional office, we use the same structure of database, when we
come to combine all data from many churches, what will heappen with the
memberID, because there will be duplicates in the member id and address ID

2. How can we assign that in the regional office, we still be able to see
their addresses, while when doing consolidation there will be problem in the
memberID and addresssID.

3. What should we change in the regional office for the table structure,
should we create a new memberID and how can we link it with the addressID,
should we make also new addressID?

Thanks for any idea.

--
H. Frank Situmorang

If head-office is importing data (not often entering data), perhaps you
could make the primary key the combination of 2 fields:
- ChurchID Number
- MemberID Number

You assign each church a number, and no church has the same MemberID more
than once, so the combination of the 2 Number fields would be unique.

--
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,

We have the membership database in many churches. The database has member
id
a primary key with the autonumber and addressID linke to the membertable
and
on the report we can see member with their address.

My question is:

1. In the regional office, we use the same structure of database, when we
come to combine all data from many churches, what will heappen with the
memberID, because there will be duplicates in the member id and address ID

2. How can we assign that in the regional office, we still be able to see
their addresses, while when doing consolidation there will be problem in
the
memberID and addresssID.

3. What should we change in the regional office for the table structure,
should we create a new memberID and how can we link it with the addressID,
should we make also new addressID?

Thanks for any idea.

--
H. Frank Situmorang

Thanks Allen for your quick answer. Do you mean to say that in the
head-office level, we need to add one church table? or can we make it in the
local church level?. I have finished desingning database for the local
church, and yes I have to admit in the caption property of any form or
report, I still make it one by one, which is a tedious work. It will be more
practical if caption property take name of the church from the table. Do you
think it is possible?

Thanks for your help.
--
H. Frank Situmorang


"Allen Browne" wrote:

If head-office is importing data (not often entering data), perhaps you
could make the primary key the combination of 2 fields:
- ChurchID Number
- MemberID Number

You assign each church a number, and no church has the same MemberID more
than once, so the combination of the 2 Number fields would be unique.

--
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,

We have the membership database in many churches. The database has member
id
a primary key with the autonumber and addressID linke to the membertable
and
on the report we can see member with their address.

My question is:

1. In the regional office, we use the same structure of database, when we
come to combine all data from many churches, what will heappen with the
memberID, because there will be duplicates in the member id and address ID

2. How can we assign that in the regional office, we still be able to see
their addresses, while when doing consolidation there will be problem in
the
memberID and addresssID.

3. What should we change in the regional office for the table structure,
should we create a new memberID and how can we link it with the addressID,
should we make also new addressID?

Thanks for any idea.

--
H. Frank Situmorang

It's certainly possible, Frank

I don't know which would approach would be better for you. If you already
have the existing church databases in place, then you could just add the new
Church table to your headquarters database, assigning a unique key to each
church. Then whenever you import data from a church, you would need the user
to choose which church it is they are importing data from, and your Append
query would append both values (the ChurchID as well as the MemberID.)

--
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
...
Thanks Allen for your quick answer. Do you mean to say that in the
head-office level, we need to add one church table? or can we make it in
the
local church level?. I have finished desingning database for the local
church, and yes I have to admit in the caption property of any form or
report, I still make it one by one, which is a tedious work. It will be
more
practical if caption property take name of the church from the table. Do
you
think it is possible?

Thanks for your help.
--
H. Frank Situmorang


"Allen Browne" wrote:

If head-office is importing data (not often entering data), perhaps you
could make the primary key the combination of 2 fields:
- ChurchID Number
- MemberID Number

You assign each church a number, and no church has the same MemberID more
than once, so the combination of the 2 Number fields would be unique.

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

We have the membership database in many churches. The database has
member
id
a primary key with the autonumber and addressID linke to the
membertable
and
on the report we can see member with their address.

My question is:

1. In the regional office, we use the same structure of database, when
we
come to combine all data from many churches, what will heappen with the
memberID, because there will be duplicates in the member id and address
ID

2. How can we assign that in the regional office, we still be able to
see
their addresses, while when doing consolidation there will be problem
in
the
memberID and addresssID.

3. What should we change in the regional office for the table
structure,
should we create a new memberID and how can we link it with the
addressID,
should we make also new addressID?

Allen,

I am so interested to know that caption can take the name from table. Could
you please be more details Allen on how can we make it work?. For the caption
of the form and report, yes it works, but how if it is for the header label
of the report. for example, in the report of active membership I have the
label in the report header as follows:

Active membership of " Jakarta Pioner Church" for the other church I should
design manually Active membership of " Bandung Hilltop Church". How can we
make it when we setup the software there is a prompt to put a name of the
church and then it will right to all caption of the form and incluing Report
header label of the church.

Thanks Allen for your kind of help.
--
H. Frank Situmorang


"Allen Browne" wrote:

It's certainly possible, Frank

I don't know which would approach would be better for you. If you already
have the existing church databases in place, then you could just add the new
Church table to your headquarters database, assigning a unique key to each
church. Then whenever you import data from a church, you would need the user
to choose which church it is they are importing data from, and your Append
query would append both values (the ChurchID as well as the MemberID.)

--
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
...
Thanks Allen for your quick answer. Do you mean to say that in the
head-office level, we need to add one church table? or can we make it in
the
local church level?. I have finished desingning database for the local
church, and yes I have to admit in the caption property of any form or
report, I still make it one by one, which is a tedious work. It will be
more
practical if caption property take name of the church from the table. Do
you
think it is possible?

Thanks for your help.
--
H. Frank Situmorang


"Allen Browne" wrote:

If head-office is importing data (not often entering data), perhaps you
could make the primary key the combination of 2 fields:
- ChurchID Number
- MemberID Number

You assign each church a number, and no church has the same MemberID more
than once, so the combination of the 2 Number fields would be unique.

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

We have the membership database in many churches. The database has
member
id
a primary key with the autonumber and addressID linke to the
membertable
and
on the report we can see member with their address.

My question is:

1. In the regional office, we use the same structure of database, when
we
come to combine all data from many churches, what will heappen with the
memberID, because there will be duplicates in the member id and address
ID

2. How can we assign that in the regional office, we still be able to
see
their addresses, while when doing consolidation there will be problem
in
the
memberID and addresssID.

3. What should we change in the regional office for the table
structure,
should we create a new memberID and how can we link it with the
addressID,
should we make also new addressID?

Let's assume you have a Church table, with fields like this:
- ChurchID AutoNumber primary key
- ChurchName Text

You have a relationship between this table and your Membership table, where
the membership primary key is the combination of the 2 fields:
- ChurchID Number matches the ChurchID in the table above
- MemberID Number the number imported from that church.

Now you use a query that has both tables, and use the query as the source
for your report. You now have the ChurchName field in the query, and so you
can show it on the report.

You might even have a ChurchName group header in the report's Sorting And
Grouping box, and so it shows as a group header in your report.

--
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,

I am so interested to know that caption can take the name from table.
Could
you please be more details Allen on how can we make it work?. For the
caption
of the form and report, yes it works, but how if it is for the header
label
of the report. for example, in the report of active membership I have the
label in the report header as follows:

Active membership of " Jakarta Pioner Church" for the other church I
should
design manually Active membership of " Bandung Hilltop Church". How can we
make it when we setup the software there is a prompt to put a name of the
church and then it will right to all caption of the form and incluing
Report
header label of the church.

Thanks Allen for your kind of help.
--
H. Frank Situmorang


"Allen Browne" wrote:

It's certainly possible, Frank

I don't know which would approach would be better for you. If you already
have the existing church databases in place, then you could just add the
new
Church table to your headquarters database, assigning a unique key to
each
church. Then whenever you import data from a church, you would need the
user
to choose which church it is they are importing data from, and your
Append
query would append both values (the ChurchID as well as the MemberID.)

"Frank Situmorang" wrote in message
...
Thanks Allen for your quick answer. Do you mean to say that in the
head-office level, we need to add one church table? or can we make it
in
the
local church level?. I have finished desingning database for the local
church, and yes I have to admit in the caption property of any form or
report, I still make it one by one, which is a tedious work. It will be
more
practical if caption property take name of the church from the table.
Do
you
think it is possible?

Thanks for your help.
--
H. Frank Situmorang


"Allen Browne" wrote:

If head-office is importing data (not often entering data), perhaps
you
could make the primary key the combination of 2 fields:
- ChurchID Number
- MemberID Number

You assign each church a number, and no church has the same MemberID
more
than once, so the combination of the 2 Number fields would be unique.

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

We have the membership database in many churches. The database has
member
id
a primary key with the autonumber and addressID linke to the
membertable
and
on the report we can see member with their address.

My question is:

1. In the regional office, we use the same structure of database,
when
we
come to combine all data from many churches, what will heappen with
the
memberID, because there will be duplicates in the member id and
address
ID

2. How can we assign that in the regional office, we still be able
to
see
their addresses, while when doing consolidation there will be
problem
in
the
memberID and addresssID.

3. What should we change in the regional office for the table
structure,
should we create a new memberID and how can we link it with the
addressID,
should we make also new addressID?