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
|
|||
|
|||
address primary key
Hello,
I have 2 tables of our church membership, member table and addrfess table In member table there is a foreign key of address table. When we come to append this table in the higher organisation, the primary key of address will conflict with the primary key of another church's address table. I have an idea to keep the address primary key is always uniqueI want to make an acronym alpha numeric. Is it posible to make an autonumber of addess table to be alpha numeric. Or my question how can we handle the uniqueness of the address primary key ( autonumber) so that when we come to an cosolidation of all churches member table, the address is also can linked in the consolidation, so we still keep track the address of all members in the Regional office of the church. We appreciate your any idea provided. Thanks in advance, -- H. Frank Situmorang |
#2
|
|||
|
|||
address primary key
On Sat, 11 Oct 2008 20:05:01 -0700, Frank Situmorang
wrote: Hello, I have 2 tables of our church membership, member table and addrfess table In member table there is a foreign key of address table. When we come to append this table in the higher organisation, the primary key of address will conflict with the primary key of another church's address table. I have an idea to keep the address primary key is always uniqueI want to make an acronym alpha numeric. Is it posible to make an autonumber of addess table to be alpha numeric. Or my question how can we handle the uniqueness of the address primary key ( autonumber) so that when we come to an cosolidation of all churches member table, the address is also can linked in the consolidation, so we still keep track the address of all members in the Regional office of the church. We appreciate your any idea provided. Thanks in advance, I would suggest a somewhat different approach. Add another field to the addresses table, identifying which church this person belongs to. Make the ChurchID and the AddressID a joint, two field primary key (and don't use autonumber for the AddressID). This will keep the record unique even across different churches. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
address primary key
Thank you John for your lightening my understanding in this database design.
Can we make like an accronym name of the church in this additional field of this address table?. I am not so clear in makeing a joint primary key to be the primary key of the address table?. How can we make it is there any sample of database on this special case?. Thanks for your help. -- H. Frank Situmorang "John W. Vinson" wrote: On Sat, 11 Oct 2008 20:05:01 -0700, Frank Situmorang wrote: Hello, I have 2 tables of our church membership, member table and addrfess table In member table there is a foreign key of address table. When we come to append this table in the higher organisation, the primary key of address will conflict with the primary key of another church's address table. I have an idea to keep the address primary key is always uniqueI want to make an acronym alpha numeric. Is it posible to make an autonumber of addess table to be alpha numeric. Or my question how can we handle the uniqueness of the address primary key ( autonumber) so that when we come to an cosolidation of all churches member table, the address is also can linked in the consolidation, so we still keep track the address of all members in the Regional office of the church. We appreciate your any idea provided. Thanks in advance, I would suggest a somewhat different approach. Add another field to the addresses table, identifying which church this person belongs to. Make the ChurchID and the AddressID a joint, two field primary key (and don't use autonumber for the AddressID). This will keep the record unique even across different churches. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
address primary key
On Sun, 12 Oct 2008 19:42:03 -0700, Frank Situmorang
wrote: Thank you John for your lightening my understanding in this database design. Can we make like an accronym name of the church in this additional field of this address table?. I am not so clear in makeing a joint primary key to be the primary key of the address table?. How can we make it is there any sample of database on this special case?. If the acronym of the church is stable and unique, yes, you can certainly use it. To create a two-field primary key add the church acronym field to the table, and fill it in (use an update query if you have existing records). The field cannot be blank if it is to be part of the key. Then open the table in design view; ctrl-click the acronym field and the current ID field, and click the Key icon on the toolbar. Don't know of any sample databases that would have just this setup, but it's a fairly common technique. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
address primary key
Hi John,
I am not an advanced user of a database, therefore I do not understand fully your explanation. Are you saying that we will have 2 primary key in the address table?, One is an existing autonumber and the other one is the Acronym joint with the autonumber?. For the acronym field in the address table, can we make it to take the data from the acronym table, and we will not allow to duplicate this field?. Suppose we have 100,000 churches alll over the world, is it ok if we make 4 character/letter acronym?. The purpose of this primary key is to keep it unique. all over the world churches. Thanks for your help. -- H. Frank Situmorang "John W. Vinson" wrote: On Sun, 12 Oct 2008 19:42:03 -0700, Frank Situmorang wrote: Thank you John for your lightening my understanding in this database design. Can we make like an accronym name of the church in this additional field of this address table?. I am not so clear in makeing a joint primary key to be the primary key of the address table?. How can we make it is there any sample of database on this special case?. If the acronym of the church is stable and unique, yes, you can certainly use it. To create a two-field primary key add the church acronym field to the table, and fill it in (use an update query if you have existing records). The field cannot be blank if it is to be part of the key. Then open the table in design view; ctrl-click the acronym field and the current ID field, and click the Key icon on the toolbar. Don't know of any sample databases that would have just this setup, but it's a fairly common technique. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
address primary key
On Mon, 13 Oct 2008 07:14:13 -0700, Frank Situmorang
wrote: Hi John, I am not an advanced user of a database, therefore I do not understand fully your explanation. Are you saying that we will have 2 primary key in the address table?, One is an existing autonumber and the other one is the Acronym joint with the autonumber?. For the acronym field in the address table, can we make it to take the data from the acronym table, and we will not allow to duplicate this field?. Suppose we have 100,000 churches alll over the world, is it ok if we make 4 character/letter acronym?. The purpose of this primary key is to keep it unique. all over the world churches. A table can have one and only one primary key - but that key can consist of one field, two fields, or even ten fields. My suggestion is that your table's key should consist of a church identifier (unique to the church) and a member ID (unique within the church). An autonumber would work within each individual church's database, but for the consolidated database the memberID should be a Long Integer; for ease of maintenance and use I'd make it a Long Integer in both the individual church database and the combined database. You'll need some VBA code to implement a "Custom Counter" to assign the member ID. I would NOT recommend a four letter church name acronym, especially if you're talking about that many churches! There would be too many duplicates (Parma First Church, Pittsburgh First Church - which gets PFC?), so you would need to use arbitrary codes unrelated to the church name. I'd use instead a table of Churches with a long integer ChurchID and the church name; in an indiviual church, the ChurchID field would simply be a long integer with its Default Value set to that church's ID. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
address primary key
Thank you John for your explanation. I see in the sample of the swithboard
items table of MS Access sample there are 2 primary keys, why it is possilbe. I am still not clear the mechanism when we come to consolidation of churhes data in the regional office of the churches. Should we add additional primary key of member?(custom counter)?. and this member table ( in the regional office level) link with the unique address primarykey? of the chruch level?' Thanks for your help. Sorry it could be my language problem too, because in Indonesia we seldom use English, we have Indonesian language. -- H. Frank Situmorang "John W. Vinson" wrote: On Mon, 13 Oct 2008 07:14:13 -0700, Frank Situmorang wrote: Hi John, I am not an advanced user of a database, therefore I do not understand fully your explanation. Are you saying that we will have 2 primary key in the address table?, One is an existing autonumber and the other one is the Acronym joint with the autonumber?. For the acronym field in the address table, can we make it to take the data from the acronym table, and we will not allow to duplicate this field?. Suppose we have 100,000 churches alll over the world, is it ok if we make 4 character/letter acronym?. The purpose of this primary key is to keep it unique. all over the world churches. A table can have one and only one primary key - but that key can consist of one field, two fields, or even ten fields. My suggestion is that your table's key should consist of a church identifier (unique to the church) and a member ID (unique within the church). An autonumber would work within each individual church's database, but for the consolidated database the memberID should be a Long Integer; for ease of maintenance and use I'd make it a Long Integer in both the individual church database and the combined database. You'll need some VBA code to implement a "Custom Counter" to assign the member ID. I would NOT recommend a four letter church name acronym, especially if you're talking about that many churches! There would be too many duplicates (Parma First Church, Pittsburgh First Church - which gets PFC?), so you would need to use arbitrary codes unrelated to the church name. I'd use instead a table of Churches with a long integer ChurchID and the church name; in an indiviual church, the ChurchID field would simply be a long integer with its Default Value set to that church's ID. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
address primary key
On Mon, 13 Oct 2008 19:50:01 -0700, Frank Situmorang
wrote: Thank you John for your explanation. I see in the sample of the swithboard items table of MS Access sample there are 2 primary keys, why it is possilbe. I am still not clear the mechanism when we come to consolidation of churhes data in the regional office of the churches. Should we add additional primary key of member?(custom counter)?. and this member table ( in the regional office level) link with the unique address primarykey? of the chruch level?' Thanks for your help. Sorry it could be my language problem too, because in Indonesia we seldom use English, we have Indonesian language. I would suggest having exactly the same design of Members table in both the regional office and the individual church databases, just for ease of maintenance. The table would have a structure like: Members ChurchID Long Integer, Primary Key MemberID Long Integer, Primary Key Surname GivenName other biographical data as appropriate In the table in Church #125 you would have (apparently redundant) records in the table, ALL of them 125 in the ChurchID field. The members would be MemberID 1, 2, 3, ..., 85 and so on; this value would be unique by itself, but the primary key would have pairs 125;1 125;2 125;3 and so on. The combination is unique, and the 125 doesn't really play a role *in this database*. But in the regional database you would have records from church 125, and church 78, and church 33. Each church's data would have its own series of MemberID's from 1 to however many members the church has. In the regional office database you would have records like 33; 1 33; 2 33; 3 .... 33; 120 78; 1 78; 2 78; 3 .... 78; 225 125; 1 125; 2 125; 3 There would be lots of Member # 1 records - but they'd all be for different ChurchIDs; and there'll be lots of Church #78 records - but they'll all have different MemberID's. The combination of the two fields will be unique. For maintenance, you could do something like export all of the records from Church 78's database to an external Text file, or dBase file; or just zip and email the entire database. The central office could then just (say) delete all records for ChurchID 78 and run an Append query to load the current set. You might want to keep a historical table of former members rather than deleting, in an archive table of some sort. Hope this helps! -- John W. Vinson [MVP] |
#9
|
|||
|
|||
address primary key
Thanks very much John, this really help me, but the idea is very helpful,
especially our Seventh Day Adventist churches have the organization hirarchy as follows starting from the lowest level: 1. Individual/local church 2. Regional Office 3. Union Office 4. Division Office 5. General Confrence ( World Office) From what I learned from you, in the member table, we should have all the above ID to be a primary key. Am I right?. The reporting system is bottom up. Is it possible to make 5 fields to be a primarykey?. Now how about the address table, should we make it like a member table? with other 5 fields to be a primary key?, so that all unique? -- H. Frank Situmorang "John W. Vinson" wrote: On Mon, 13 Oct 2008 19:50:01 -0700, Frank Situmorang wrote: Thank you John for your explanation. I see in the sample of the swithboard items table of MS Access sample there are 2 primary keys, why it is possilbe. I am still not clear the mechanism when we come to consolidation of churhes data in the regional office of the churches. Should we add additional primary key of member?(custom counter)?. and this member table ( in the regional office level) link with the unique address primarykey? of the chruch level?' Thanks for your help. Sorry it could be my language problem too, because in Indonesia we seldom use English, we have Indonesian language. I would suggest having exactly the same design of Members table in both the regional office and the individual church databases, just for ease of maintenance. The table would have a structure like: Members ChurchID Long Integer, Primary Key MemberID Long Integer, Primary Key Surname GivenName other biographical data as appropriate In the table in Church #125 you would have (apparently redundant) records in the table, ALL of them 125 in the ChurchID field. The members would be MemberID 1, 2, 3, ..., 85 and so on; this value would be unique by itself, but the primary key would have pairs 125;1 125;2 125;3 and so on. The combination is unique, and the 125 doesn't really play a role *in this database*. But in the regional database you would have records from church 125, and church 78, and church 33. Each church's data would have its own series of MemberID's from 1 to however many members the church has. In the regional office database you would have records like 33; 1 33; 2 33; 3 .... 33; 120 78; 1 78; 2 78; 3 .... 78; 225 125; 1 125; 2 125; 3 There would be lots of Member # 1 records - but they'd all be for different ChurchIDs; and there'll be lots of Church #78 records - but they'll all have different MemberID's. The combination of the two fields will be unique. For maintenance, you could do something like export all of the records from Church 78's database to an external Text file, or dBase file; or just zip and email the entire database. The central office could then just (say) delete all records for ChurchID 78 and run an Append query to load the current set. You might want to keep a historical table of former members rather than deleting, in an archive table of some sort. Hope this helps! -- John W. Vinson [MVP] |
#10
|
|||
|
|||
address primary key
On Tue, 14 Oct 2008 02:01:01 -0700, Frank Situmorang
wrote: Thanks very much John, this really help me, but the idea is very helpful, especially our Seventh Day Adventist churches have the organization hirarchy as follows starting from the lowest level: 1. Individual/local church 2. Regional Office 3. Union Office 4. Division Office 5. General Confrence ( World Office) From what I learned from you, in the member table, we should have all the above ID to be a primary key. Am I right?. The reporting system is bottom up. Is it possible to make 5 fields to be a primarykey?. Now how about the address table, should we make it like a member table? with other 5 fields to be a primary key?, so that all unique? Well, you don't need a variable field for level 5, since there's only one General Conference (unless you plan to expand this database to include us Presbyterians)! I'd actually *not* use a four field primary key; it gets pretty hard to use especially if you have related tables (such as group memberships, donations, etc.) I'm not sure how much information you want to propagate "up" to the regional, union etc. offices; it might be better to have just the ChurchID and MemberID as a joint primary key, and have fields in the (separate) Churches table to indicate which higher level offices that church is in. If the Regional (union, division, world) office needs to know addresses, then it would be sufficient to also have ChurchID and MemberID as a two field primary key. Have you checked with the General Conference to see if they already *have* a member database? I would guess that they do. If not, maybe they'd be interested; but there are enough Seventh Day Adventist churches and members that you should really consider implementing this in SQL/Server (with an Access frontend) rather than in a native Access database. -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|