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
  #11  
Old April 23rd, 2010, 08:22 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Design for future merging

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@uwe wrote in message
news:a6f8e28a53211@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/For...esign/201004/1



  #12  
Old April 23rd, 2010, 08:30 PM posted to microsoft.public.access.tablesdbdesign
Rlong via AccessMonster.com
external usenet poster
 
Posts: 7
Default Design for future merging - more PIMPING

Hi Steve and John,

Thanks for both of your posts. I appreciate the offer to consult, and the
warning. At this point I'm not interested in the consulting avenue, but would
prefer to continue with the newsgroup so that I can learn how to do this in
the process. I'd ask that this thread not turn into a discussion of for-pay
services as some others have. Again...thanks to you both.

If anyone has any additional thoughts related to my last post I'd be keen to
hear them.

Thanks.



John... Visio MVP wrote:
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

[quoted text clipped - 9 lines]

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


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

  #13  
Old April 24th, 2010, 12:00 AM posted to microsoft.public.access.tablesdbdesign
Stop$teve
external usenet poster
 
Posts: 76
Default Design for future merging


"Steve" schreef in bericht ...
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





You are sooooo pathetic.. !!


--
Get lost $teve. Go away... far away....

Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at
http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 12.000 pageloads... it's a shame !!)

Arno R


  #14  
Old April 24th, 2010, 06:34 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Design for future merging - more PIMPING

Study my post; I gave you the process you need to use.

Steve


"Rlong via AccessMonster.com" u58125@uwe wrote in message
news:a6f959609891c@uwe...
Hi Steve and John,

Thanks for both of your posts. I appreciate the offer to consult, and the
warning. At this point I'm not interested in the consulting avenue, but
would
prefer to continue with the newsgroup so that I can learn how to do this
in
the process. I'd ask that this thread not turn into a discussion of
for-pay
services as some others have. Again...thanks to you both.

If anyone has any additional thoughts related to my last post I'd be keen
to
hear them.

Thanks.



John... Visio MVP wrote:
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

[quoted text clipped - 9 lines]

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


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



  #15  
Old April 24th, 2010, 08:05 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
...
Study my post; I gave you the process you need to use.

Steve



and the answer to that question is still...





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 10:41 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.