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  

Design for future merging



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2010, 11:16 PM posted to microsoft.public.access.tablesdbdesign
Rlong via AccessMonster.com
external usenet poster
 
Posts: 7
Default Design for future merging

I've created a small but somewhat complex relational database that uses
autonumber fields in higher level tables as primary keys to link with foreign
keys in lower level tables. At this point I have up to 5 levels of tables.
I'd like to copy this database for use at 4 other remote sites, with the
ultimate intention of merging all 5 back together after a few months of data
entry. I've read quite a bit about how to merge databases that weren't
originally designed with future merges in mind, and this ends up being quite
complex with so many levels and autonumber-dependent tables. I'm wondering if
there is a way that I can create the duplicate databases from the outset that
would make future merging easier?

For instance, by causing the autonumbers at each different site to either
start at a particular point (e.g., one site be the 100000s and another the
200000s), or, by using the "random" setting for autonumber such that no two
autonumbers in the same table were identical (although my sense is that
"random" introduces its own problems).

Any thoughts on how to make the future merge easier would be helpful.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1

  #2  
Old April 23rd, 2010, 12:30 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Design for future merging

I didn't notice which version of Access you are using. This is important
because the Replication feature went away for the ?2007 version. If you're
up to it, that might be a way if you're using the earlier (pre- 2007)
versions.

An alternate approach might be to add in a single field that holds the
location. The combination of your autonumber PLUS the
location/facility/site/... will give you a way to identify which records
belong to which site.

Now, if you were trying to use Access Autonumbers as sequence numbers,
you're in for a bit of pain. Access Autonumbers are intended to uniquely
identify rows. That's it! Not guaranteed sequential, can/will have missing
values.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Rlong via AccessMonster.com" u58125@uwe wrote in message
news:a6ee3a8f8ad28@uwe...
I've created a small but somewhat complex relational database that uses
autonumber fields in higher level tables as primary keys to link with
foreign
keys in lower level tables. At this point I have up to 5 levels of tables.
I'd like to copy this database for use at 4 other remote sites, with the
ultimate intention of merging all 5 back together after a few months of
data
entry. I've read quite a bit about how to merge databases that weren't
originally designed with future merges in mind, and this ends up being
quite
complex with so many levels and autonumber-dependent tables. I'm wondering
if
there is a way that I can create the duplicate databases from the outset
that
would make future merging easier?

For instance, by causing the autonumbers at each different site to either
start at a particular point (e.g., one site be the 100000s and another the
200000s), or, by using the "random" setting for autonumber such that no
two
autonumbers in the same table were identical (although my sense is that
"random" introduces its own problems).

Any thoughts on how to make the future merge easier would be helpful.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1



  #3  
Old April 23rd, 2010, 01:37 AM posted to microsoft.public.access.tablesdbdesign
Rlong via AccessMonster.com
external usenet poster
 
Posts: 7
Default Design for future merging

Thanks, Jeff. Yes, I am using 2007. I'm not sure what the "replication"
feature was, but it sounds like it isn't an option anyway for 2007.

So, regarding the combination field--I'm unclear how this would work, as I
didn't think you could create a "calculated field" in a table. And, assuming
I did do this, would I need to do this for all the primary key/foreign key
combinations manually throughout the database? As you can tell, I'm on the
newer end of the continuum when it comes to database construction, and have
little coding experience--most of what I've done is menu-driven.

Thanks

Jeff Boyce wrote:
I didn't notice which version of Access you are using. This is important
because the Replication feature went away for the ?2007 version. If you're
up to it, that might be a way if you're using the earlier (pre- 2007)
versions.

An alternate approach might be to add in a single field that holds the
location. The combination of your autonumber PLUS the
location/facility/site/... will give you a way to identify which records
belong to which site.

Now, if you were trying to use Access Autonumbers as sequence numbers,
you're in for a bit of pain. Access Autonumbers are intended to uniquely
identify rows. That's it! Not guaranteed sequential, can/will have missing
values.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

I've created a small but somewhat complex relational database that uses
autonumber fields in higher level tables as primary keys to link with

[quoted text clipped - 22 lines]

Thanks


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1

  #4  
Old April 23rd, 2010, 01:48 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Design for future merging

I think that you will need to be thinking about your "data" mission and data
specifics with all of these databases as a being the core of answering this
question. (or maybe you have already and didn't describe it to us.)

For example:

Are these different locations editing records that are for the same entity
(e.g. updating data for a particular person at multiple locations)

Are these different locations just adding records, or are they editing
existing records? If it's the latter, what will they be looking at to edit?


Etc.

Once you get those kinds of things decided, then those decision should how
you created your structure.


Replication was, in essence, to manage having, (and, more to the point,
editing on) multiple copies of the same database.



  #5  
Old April 23rd, 2010, 06:01 PM posted to microsoft.public.access.tablesdbdesign
Rlong via AccessMonster.com
external usenet poster
 
Posts: 7
Default Design for future merging

Thanks for the continued input. The different locations are simply entering
new data, and won't need to see or edit data from the other locations or the
base location. After 3 months of data entry, each offsite database will
ideally be brought into the base database and use at other locations will be
discontinued.

Thanks.


Fred wrote:
I think that you will need to be thinking about your "data" mission and data
specifics with all of these databases as a being the core of answering this
question. (or maybe you have already and didn't describe it to us.)

For example:

Are these different locations editing records that are for the same entity
(e.g. updating data for a particular person at multiple locations)

Are these different locations just adding records, or are they editing
existing records? If it's the latter, what will they be looking at to edit?


Etc.

Once you get those kinds of things decided, then those decision should how
you created your structure.

Replication was, in essence, to manage having, (and, more to the point,
editing on) multiple copies of the same database.


--
Message posted via http://www.accessmonster.com

  #6  
Old April 23rd, 2010, 06:38 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Design for future merging

Maybe I'm misunderstanding...

Here's my view of the data in your combined db:

ID Location blah, blah, blah

1 East
2 East
3 East
2 North
3 North
1 West
2 West
4 West
1 South
2 South
3 South
4 South
99 South

Notice that you can have multiple identical IDs (but also note that these
CAN NOT be autonumber type in the combined table!), distinguishable by their
Locations.

To do this, I would leave the Locations/sites doing their thing with their
own Autonumbers, then use a query to append the records into my "master"
table. When I appended the records, I'd be putting the ID numbers into a
new Numeric/LongInt field (that's the counterpart/equivalent of what
Autonumber generates).

Again, DO NOT attempt to append autonumbers (from Locations) into an
autonumber (in main table) field ... and Access won't let you duplicate an
already used Autonumber, so this might be moot!

Good luck!


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Rlong via AccessMonster.com" u58125@uwe wrote in message
news:a6ef7556ec99c@uwe...
Thanks, Jeff. Yes, I am using 2007. I'm not sure what the "replication"
feature was, but it sounds like it isn't an option anyway for 2007.

So, regarding the combination field--I'm unclear how this would work, as I
didn't think you could create a "calculated field" in a table. And,
assuming
I did do this, would I need to do this for all the primary key/foreign key
combinations manually throughout the database? As you can tell, I'm on the
newer end of the continuum when it comes to database construction, and
have
little coding experience--most of what I've done is menu-driven.

Thanks

Jeff Boyce wrote:
I didn't notice which version of Access you are using. This is important
because the Replication feature went away for the ?2007 version. If
you're
up to it, that might be a way if you're using the earlier (pre- 2007)
versions.

An alternate approach might be to add in a single field that holds the
location. The combination of your autonumber PLUS the
location/facility/site/... will give you a way to identify which records
belong to which site.

Now, if you were trying to use Access Autonumbers as sequence numbers,
you're in for a bit of pain. Access Autonumbers are intended to uniquely
identify rows. That's it! Not guaranteed sequential, can/will have
missing
values.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

I've created a small but somewhat complex relational database that uses
autonumber fields in higher level tables as primary keys to link with

[quoted text clipped - 22 lines]

Thanks


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1



  #7  
Old April 23rd, 2010, 07:36 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Design for future merging

I have merged remote databases into a master central database many times.
The key is to append the data in higher level tables from the remote
database into the the corresponding higher level table in the master
database. This assigns a value for the primary key in the master table. Then
append the data in lower level tables from the remote database into the the
corresponding lower level table in the master database. Now you need to
update the foreign key in the lower level tables to the value of the
corresponding primary key in the master table. I would like to offer to
automate your database to be able to do this. I will charge a reasonable fee
depending on the number of tables and relationships in your database. If you
want my help, contact me.

Steve





"Rlong via AccessMonster.com" u58125@uwe wrote in message
news:a6ee3a8f8ad28@uwe...
I've created a small but somewhat complex relational database that uses
autonumber fields in higher level tables as primary keys to link with
foreign
keys in lower level tables. At this point I have up to 5 levels of tables.
I'd like to copy this database for use at 4 other remote sites, with the
ultimate intention of merging all 5 back together after a few months of
data
entry. I've read quite a bit about how to merge databases that weren't
originally designed with future merges in mind, and this ends up being
quite
complex with so many levels and autonumber-dependent tables. I'm wondering
if
there is a way that I can create the duplicate databases from the outset
that
would make future merging easier?

For instance, by causing the autonumbers at each different site to either
start at a particular point (e.g., one site be the 100000s and another the
200000s), or, by using the "random" setting for autonumber such that no
two
autonumbers in the same table were identical (although my sense is that
"random" introduces its own problems).

Any thoughts on how to make the future merge easier would be helpful.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1



  #8  
Old April 23rd, 2010, 07:36 PM posted to microsoft.public.access.tablesdbdesign
Rlong via AccessMonster.com
external usenet poster
 
Posts: 7
Default Design for future merging

Hi Jeff,

Great...really helpful! You're not misunderstanding--you're right on track.
The combined file doesn't yet look like that because the ID field is, indeed,
an autonumber. So, if I understand you when you say:

"When I appended the records, I'd be putting the ID numbers into a new
Numeric/LongInt field (that's the counterpart/equivalent of what Autonumber
generates)."

...you mean that this new field would end up getting the original autonumbers
from the various locations, but the Autonumber ID field in the combined file
would write new autonumbers to any records that were added from other
locations.

So, this leads me to wonder what happens to all the related tables for the
various locations, because all the PK/FK links are using the autonumber field
as the PK and linking to this autonumber for the FK. Is there a way to get
all the FKs to cascade the changes (I've seen reference to cascading) that
will be introduced when the locations are assigned new autonumbers in the
combined file? Or, will I need to migrate the PK in each table to the new
Numeric/LongInt field that I've created?

Again, thanks...



Jeff Boyce wrote:
Maybe I'm misunderstanding...

Here's my view of the data in your combined db:

ID Location blah, blah, blah

1 East
2 East
3 East
2 North
3 North
1 West
2 West
4 West
1 South
2 South
3 South
4 South
99 South

Notice that you can have multiple identical IDs (but also note that these
CAN NOT be autonumber type in the combined table!), distinguishable by their
Locations.

To do this, I would leave the Locations/sites doing their thing with their
own Autonumbers, then use a query to append the records into my "master"
table. When I appended the records, I'd be putting the ID numbers into a
new Numeric/LongInt field (that's the counterpart/equivalent of what
Autonumber generates).

Again, DO NOT attempt to append autonumbers (from Locations) into an
autonumber (in main table) field ... and Access won't let you duplicate an
already used Autonumber, so this might be moot!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Thanks, Jeff. Yes, I am using 2007. I'm not sure what the "replication"
feature was, but it sounds like it isn't an option anyway for 2007.

[quoted text clipped - 39 lines]

Thanks


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1

  #9  
Old April 23rd, 2010, 07:41 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Design for future merging

IMHO the best way would be to add a fixed number (unique to the source
location and larger than the total number of records) ) onto each PK at the
time of importing them into the main database.

Sincerely,

Fred

  #10  
Old April 23rd, 2010, 07:46 PM posted to microsoft.public.access.tablesdbdesign
John... Visio MVP
external usenet poster
 
Posts: 900
Default Design for future merging - more PIMPING

"Steve" wrote in message
...
I have merged remote databases into a master central database many times.
The key is to append the data in higher level tables from the remote
database into the the corresponding higher level table in the master
database. This assigns a value for the primary key in the master table.
Then append the data in lower level tables from the remote database into
the the corresponding lower level table in the master database. Now you
need to update the foreign key in the lower level tables to the value of
the corresponding primary key in the master table. I would like to offer to
automate your database to be able to do this. I will charge a reasonable
fee depending on the number of tables and relationships in your database.
If you want my help, contact me.

Steve










Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP



 




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 05:51 PM.


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