Pimary key when consolidated

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

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

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.
 
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.
 
Back
Top