Design for future merging
in [Tables]
|
Prev: Creating links
Next: Automatic Annual Leave
From: Jeff Boyce on 23 Apr 2010 13:38 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(a)uwe> wrote in message news:a6ef7556ec99c(a)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/Forums.aspx/access-tablesdbdesign/201004/1 >
From: Rlong via AccessMonster.com on 23 Apr 2010 14:36 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/Forums.aspx/access-tablesdbdesign/201004/1
From: Steve on 23 Apr 2010 14:36 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 santus(a)penn.com "Rlong via AccessMonster.com" <u58125(a)uwe> wrote in message news:a6ee3a8f8ad28(a)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/Forums.aspx/access-tablesdbdesign/201004/1 >
From: Fred on 23 Apr 2010 14:41 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
From: Jeff Boyce on 23 Apr 2010 15:22
Oho! I don't recall you mentioning "related records". That will throw a monkey wrench in, unless you also tag those related records with their respective locations. And yes, you could still have an Autonumber field on your new, merged table .... but it would be its own thing, unrelated to the original (auto)numbers from the field. Good luck! Jeff "Rlong via AccessMonster.com" <u58125(a)uwe> wrote in message news:a6f8e28a53211(a)uwe... > 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/Forums.aspx/access-tablesdbdesign/201004/1 > |