A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VBA for Primary Key



 
 
Thread Tools Display Modes
  #1  
Old October 27th, 2008, 10:15 AM posted to microsoft.public.access.tablesdbdesign
Frank Situmorang[_2_]
external usenet poster
 
Posts: 340
Default VBA for Primary Key

Hello,

Since a multifield primary key is more comlicated to use, therefore I plan
to have a surrogate/ 1field PK, but with the automatic fill by the VBA as
follows

1. 2 first digit will be Division ID
2. 2 next digits will be Union ID
3. 2 Next digits will be Regional ID
4. 3. Next digits will be Church ID
5. the rest will be increment by 1

So if my church has the following hierarcchy:
DvisionID: 1
Union ID : 2
Regional ID:3
ChruchID ID;4
Household/AddressID: incrment by 1, this is the first record.

What is the VBA if I would like it to show up like this:
01_02_03_004_1

I apreciate your help

--
H. Frank Situmorang
  #2  
Old October 27th, 2008, 11:03 AM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default VBA for Primary Key

hi Frank,

Frank Situmorang wrote:
Since a multifield primary key is more comlicated to use, therefore I plan
to have a surrogate/ 1field PK, but with the automatic fill by the VBA as
follows


So if my church has the following hierarcchy:
DvisionID: 1
Union ID : 2
Regional ID:3
ChruchID ID;4
Household/AddressID: incrment by 1, this is the first record.

What is the VBA if I would like it to show up like this:
01_02_03_004_1

Yuck. This is redundancy at its best. Normally you should have this
structure using surrogate keys:

Division: ID, ...
Union: ID, Division_ID, ..
Region: ID, Union_ID, ..
Church: ID, Region_ID, ..

The key concept of surrogate keys is about _not_ to carry any information.


mfG
-- stefan --
  #3  
Old October 27th, 2008, 12:10 PM posted to microsoft.public.access.tablesdbdesign
Frank Situmorang[_2_]
external usenet poster
 
Posts: 340
Default VBA for Primary Key

Thanks Stefan for your response. Let me tell you that the purpose of this is
to keep the address of the member PK alwasy unique when we cosolidated the
data in the upper level of organization or office.

The very low level office is local church, then chruch will send it;s member
data to higher office which is regional to consolidate data of all churches
on its region. Then Regional officie will send it's data to higher level
office wchich is Union Office...and so forth upto Division Office..and
consolidated all divisions to have the world total members.

In any of the level office, the address is always unique, therefore we know
the addresses of the members when we consolidated it.

But if we do not do like that, let me show you the sample, Address Mr. A in
church P will start with the number 1, then when I give the blank database to
church Q and the address of Mr. B will also start wtih number 1. When both
chrurches sent their data to regional office, the primary key will
conflict./duplicate. We can not assume to have it with the next number in the
regional office, but it is already said number 1 also in the Foregn Key of
the members table.

Thanks for your idea, if you stil can help me plasea. I am just a self
study, my specailty is accountancy
--
H. Frank Situmorang


"Stefan Hoffmann" wrote:

hi Frank,

Frank Situmorang wrote:
Since a multifield primary key is more comlicated to use, therefore I plan
to have a surrogate/ 1field PK, but with the automatic fill by the VBA as
follows


So if my church has the following hierarcchy:
DvisionID: 1
Union ID : 2
Regional ID:3
ChruchID ID;4
Household/AddressID: incrment by 1, this is the first record.

What is the VBA if I would like it to show up like this:
01_02_03_004_1

Yuck. This is redundancy at its best. Normally you should have this
structure using surrogate keys:

Division: ID, ...
Union: ID, Division_ID, ..
Region: ID, Union_ID, ..
Church: ID, Region_ID, ..

The key concept of surrogate keys is about _not_ to carry any information.


mfG
-- stefan --

  #4  
Old October 27th, 2008, 02:01 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default VBA for Primary Key

hi Frank,

Frank Situmorang wrote:
The very low level office is local church, then chruch will send it;s member
data to higher office which is regional to consolidate data of all churches
on its region. Then Regional officie will send it's data to higher level
office wchich is Union Office...and so forth upto Division Office..and
consolidated all divisions to have the world total members.

So you have

Division: ID, ... PK(ID)
Union: ID, Division_ID, .. PK(ID)
Region: ID, Union_ID, .. PK(ID)
Church: ID, Region_ID, .. PK(ID)
1, 1, "Church P"
1, 2, "Church Q"

as invariant structural tables.

Your local offices fill data in your address table:

Address: ID, Church_ID, ...

But if we do not do like that, let me show you the sample, Address Mr. A in
church P will start with the number 1, then when I give the blank database to
church Q and the address of Mr. B will also start wtih number 1. When both
churches sent their data to regional office, the primary key will
conflict./duplicate.

Address (Church P): 1, 1, "Mr. A"
Address (Church Q): 1, 2, "Mr. B"

The surrogat key ID in church is sufficent for your local office, but
when you are consolidating the data your are copying data into another
structural scheme which has _other_ primary keys. For the table Church
it is then a combined key consisting of ID and Region_ID or you need an
other table to hold the consolidated data:

Address (Consolidated): ID, Original_ID, Church_ID, ...

Depending on your needs you may consider using a GUID as unique id, e.g.

http://www.devx.com/dbzone/Article/10167/0/page/3


mfG
-- stefan --
  #5  
Old October 27th, 2008, 02:59 PM posted to microsoft.public.access.tablesdbdesign
Klatuu[_3_]
external usenet poster
 
Posts: 396
Default VBA for Primary Key

I would highly recommend you use an Autonumber primary key. All the other
fields now included should just be constrained to require a valid value.

"Frank Situmorang" wrote in message
...
Thanks Stefan for your response. Let me tell you that the purpose of this
is
to keep the address of the member PK alwasy unique when we cosolidated
the
data in the upper level of organization or office.

The very low level office is local church, then chruch will send it;s
member
data to higher office which is regional to consolidate data of all
churches
on its region. Then Regional officie will send it's data to higher level
office wchich is Union Office...and so forth upto Division Office..and
consolidated all divisions to have the world total members.

In any of the level office, the address is always unique, therefore we
know
the addresses of the members when we consolidated it.

But if we do not do like that, let me show you the sample, Address Mr. A
in
church P will start with the number 1, then when I give the blank database
to
church Q and the address of Mr. B will also start wtih number 1. When both
chrurches sent their data to regional office, the primary key will
conflict./duplicate. We can not assume to have it with the next number in
the
regional office, but it is already said number 1 also in the Foregn Key of
the members table.

Thanks for your idea, if you stil can help me plasea. I am just a self
study, my specailty is accountancy
--
H. Frank Situmorang


"Stefan Hoffmann" wrote:

hi Frank,

Frank Situmorang wrote:
Since a multifield primary key is more comlicated to use, therefore I
plan
to have a surrogate/ 1field PK, but with the automatic fill by the VBA
as
follows


So if my church has the following hierarcchy:
DvisionID: 1
Union ID : 2
Regional ID:3
ChruchID ID;4
Household/AddressID: incrment by 1, this is the first record.

What is the VBA if I would like it to show up like this:
01_02_03_004_1

Yuck. This is redundancy at its best. Normally you should have this
structure using surrogate keys:

Division: ID, ...
Union: ID, Division_ID, ..
Region: ID, Union_ID, ..
Church: ID, Region_ID, ..

The key concept of surrogate keys is about _not_ to carry any
information.


mfG
-- stefan --



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.