If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Pimary key when consolidated
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 |
#3
|
|||
|
|||
Pimary key when consolidated
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 |
#4
|
|||
|
|||
Pimary key when consolidated
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? |
#5
|
|||
|
|||
Pimary key when consolidated
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? |
#6
|
|||
|
|||
Pimary key when consolidated
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? |
Thread Tools | |
Display Modes | |
|
|