Combining Autonumbered Tables

[matthewnsarah07]

I am currently using a staff holiday database to collect holiday requests from 7 working locations.

Due to very poor network speeds early on I created a table to be stored locally for each of the 7 locations to aid with speed issues, each has the primary key field [RequestNumber] which is an incremental autonumber.

With network speeds improving how can I merge all these tables (some 3500+ records) into one table - staff use the RequestNumber for reference is there any way to preserve this?

 

[marleymanner1]

Matt,

I'm pretty sure there is a Microsoft article on this, but have you tried changing the autonumber fields to just long integer fields first? I can't remember if you can do that or not. But if you can, you can simply change them, and then merge them all into one table. Then after that, merge them to a table that has a blank autonumber field in it. I think I have seen that question here before, so a search on that technique would yield you good results I think.

 

[Premy]

Well u can create a compound PK in a new table which combines RequestNumber and EmpoyeeID, then add the existing data to it thru an append query. This will thus identify each record by combining RequestNumber and EmployeeID and duplicate RequestNumbers will be allowed.

Thing is that u have to update all references in your application that use RequestNumber. For example, if you have some code somewhere that says "Select... WHERE RequestNumber=" & Me.cboRequestNumber", it should now read: "Select... WHERE RequestNumber=" & Me.cboRequestNumber & " AND EmployeeID=" & Me.cboEmployee"