Appending tables with autonumber fields

[KerryL]

I have a situation where separate sites use copies of the same database. To run reports on the consolidated data, I will need to merge data from each database into one master copy.

If the tables in each separate database use autonumbering for the PK, won't this cause problems for me when I try and combine the data?

For example:

If tblDetroitCust and tblChicagoCust both use autonumbers for their PK, and both tables are appended to tblMaster, what will happen when records containing the same PK autonumber are appended to the Master table?


How do I avoid this?
If I assign a code to each location and start the autonumber sequence accordingly, will that work?

Detroit = 10, Chicago = 20
Detroit autonumber starts at 10000
Chicago autonumber starts at 20000

What's the best way to avoid matching PK autonumbers when appending records from multiple tables into one master table?

 

[mp9]

Your suggestion will work until Chicago wants to increment beyond 19999, so you need to ask yourself if this is likely and in what timeframe? You could start them from 1 and 1000000 instead to give yourself more room to manoeuvre.

However, what you really want to do is look into replication, whereby you have a central "master" db and many replicated instances of it (in your example, one for each site - however, this signficantly increases the complexity of your database. But from a technical purist's standpoint, this is the way to go.

The Access help file has plenty on replication.

 

[BNPMike]

What's the best way to avoid matching PK autonumbers when appending records from multiple tables into one master table?

Use replication, available in Jet and MS SQL Server (possibly not in MSDE).

 

[KerryL]

Thank you.

I think I understand what you mean by replication. The problem with that is there are 30 sites, all of which need to use the database but only need to see their entries.

So if I replicate a master database, each site will see tons of data they don't need to see. In fact, for security reasons they're not supposed to see the data from other sites.

 

[GingerR]

when you merge the data, merge it into a table that has an additional field called i.e "SITE". If you are just running a bunch of append queries to merge it all together, fill this field with a SITE code which you keep in a separte table "SITES" ("CHI","DET", etc). Then make the primary key consist of two fields, SITE and ID.

 

[willir]

So Kerry

You have two resolutions...
- Use the replication feature within Access. This works, but proceed carefully, and make sure you ahve backups. Although it is a "feature", it is really an advanced feature that has caused problems for the unprepared. Several things happend when you move to replications...
-- You have a "master" database and the replications. The "master" is just that, the master -- nothing runs without the "master".
-- When you replicate, your autonumber system will change, either to a randomized numbering system (same size field as the autonumber, but you will see large and small negative and small numbers), or move to a ReplicationID which is a more like a certificate alphanumeric, and it is large. Bottom line here is that you will probably want to hide the primary key at this point -- otherwise it will confuse the end user.
-- When you replicate, a two-way conversation occurs as the master and replica negotiate what records to exchange. You may get conflicts (less likely if you use ReplicationID) in which case a decision is made on which update is correct.
-- Because of the way replication works, one replica and the master at a time, it may take a while to trickle the update from Replica-B to Replica-A via the Master.
-- Protect that master. Otherwise, it could be expensive and/or frustrating.

Use the site + number to generate the primary key. Although coding is involved, this would be the approach I would use since it would allow me to separate things at the primary key level.

Here is something real simple to consider for this "trick"...

tblTest
TestPrimaryID - primary key, text, 15 long
AutoNumID - autonumber, long interger, indexed -- see discussion
SiteCode - text, 5 characters
MyRealData - then your fields for your real data

Discussion (reverse order):
SiteCode - Not necessary in your database, may be best to keep this on an administrative table and then read the site code into global / public memory so it is always accessible to all modules, forms, report, etc. For the purpose of this example, I defined in the table, and defined as a text string with 5 characters.
AutoNumID - I struggled with this on indexing. You want to prevent duplication within the table, but allow for duplication when merging data for the different tables. My apporach would be to index the field and not allow for duplicates. Then, when merging data for different sites, to exclude the field, or merge it into a table that allows for duplicates. YES, it is possible to use Autonumber and notuse it as the primary key.
TestPrimaryID - This is the primary key. It is a text string consisting of SiteCode + AutoNumID that needs to be defined large enough to accommodate the two fields. In this example 5 characters + long interger (10) = 15.

Coding:
When I though about it, the coding became extremely simple if you take advantage of the BeforeInsert event procedure. This event kicks in as soon as you start to type in data.

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.AutoNumID.Requery
Me.SiteCode = "TEST"
TestPrimaryID = Me.SiteCode & (Format(Me.AutoNumID, "0000000000"))

End Sub

Incredibly simple. As soon as data is entered, the BeforeInsert procedure kicks-in.
- The Autonumber in Me.AutoNumID is generated with the requery method.
- The SiteCode is grabbed. In this case, I "hardcode" it for the table. In your case, the best way would be store the value in public memory (when you start your database).
- The primary key key is generated by joining the site + autonumber.

Richard

 

[KerryL]

Thank you very much--great explanation.

If I were writing this DB from scratch the replication might make sense, but I'm trying to make a modification to it (actually to 30 copies of it) that will make it as easy as possible to consolidate the data for reporting.

Each site has a copy of the existing DB, but once a month the data is supposed to be combined and reported on. Each of the 30 DBs exist in a different folder on a network drive (the 30 sites are remote) so I'm trying to create a way to easily append tables from each DB into the master DB for reporting purposes.

I'm not sure if replication would be the way to go when the replicated DB's are physically located all around the state, but like I said, I'm just trying to combine data from identical tables. The data from each site is different, so there won't be file contention issues, etc.

The main concern I have is how to append data from identically-designed tables without the autonumber primary key fields butting heads.

The only thing I can think of is to assign numbers for each site and begin the autonumber sequence accordingly:
Detroit = 15, so autonumber begins at 15000000
Lansing = 18, so autonumber begins at 18000000

Does that make sense?

 

[GingerR]

Kerry--again that's not your only solution. What about what I suggested? It's pretty similar, but you don't have to cludge a key together, then re-parse out the site code (your number code). It would just retain the orig autonumber but includes a unique cite code in a separate field. The combination of the two define your primary key. Then if you have to build reports BY Site, you already have that info parsed out into its own field. Pretty much the same thing you are suggesting, but I see it as cleaner when I think of the ease of building reports and such down the road.

Do you have a specific reason why this won't work for you?

 

[KerryL]

GingerR,
I apologize for missing your first response.

Each copy of the DB already contains a unique two-digit site code so I think your idea will work. The problem is, I've never created a primary key that is a combination of two different fields so I'm not sure how to do it.

 

[GingerR]

In the design of the table, choose the two fields and click on the key in the toolbar.