Unanswered: Import/Export filtered data (with auto# Prim Key) in multiple linked tables
Hello all. First time for me here. Feeling like a small fish in a big pond. Hoping I can get some help. Before I get to my main question I'll provide some background info.
Using XP Pro SP2, Access 2003 (11.6566.8132) SP2.
I'm in the Army and my job is Army Band (yes I'm a musician). But because I'm technically inclined music is not the only thing important to me. I notice data duplication in many areas of the paper-pushing part of my job. So I started messing with Access and I've come a long way. As far as organizing what one unit (my unit) needs to do, I can do that no problem.
I use one main table to hold all data that is "1 to 1" in nature, and I use seperate linked tables (many to 1) for other things like training data, phone numbers, ect, anything that could have multiple records of data per soldier.
My problem is 2 fold. Initially I'm using a user name as a primary key in most of my tables instead of autonumber. Some other people in other army band units who claim to know a lot about datasing have warned me about the danger in that, ie. that data being copied many times over making the database huge, or what if the user name changes.
I feel that I can handle the user name changing with my current knowledge, but I would like to be able to keep the database small as well. What is keeping me from using the autonumber is this:
Problem 1: I'm looking for a way to export linked data across multiple tables ... or in other words, filter out one soldier's data and record history, export it into a nice neat file, and import it to another database with the same structure while maintaining the linked data.
Problem 2: For the sake of efficiency I want to use the autonumber as my main primary key, but what if the destination database already has that autonumber taken? How can the number be changed while maintaining the links in the linked data?
The very act of creating this database is duplicating systems that the Army already uses. Those systems however do not automate statistical reports that we need (that are prefect for a computer application to do), thus the desire for a database of our own... BUT... when a person moves from one station to another, Import/Export is important, otherwise data entry will increase with each move, and that will waste time and the possibility of user error will increase exponentially.
The systems we usually use go down quite regularly and keep changing. That makes doing our paper-work very frustrating. Making our own database would be duplicating a lot of data, but once it gets going it would be easier to manage getting our jobs done, so this one instance of data duplication is acceptable.
It's not practical to use a web based application and a centralized server because some units have slightly different requirements, and the usage of custom reports is a must. Also, this really isn't our job and we don't have the funds or time to implement something at that large of a scale.
Eventually, my intent is to mirror the Army's sytems (seeing as the data usually stays the same, just the format changes), and then one day hopefully be able to link to the larger systems to end our data duplication once and for all. Until that happens, this is the best idea I can think of for the time being.
Each unit will manage less than 500 records (for history sake) so I figure Access should be able to handle that much.
I know that the Army may switch it's computer packaged software to Access 2007 some day, but I'll cross that bridge when it comes. For now I'm just sticking with Access 2003.
Well as for problem 2, you can't modify the value of an autonumber, so if that's a problem, it may be too late to use an autonumber for your PKF.
As for worrying about size, I wouldn't. How big is your database now? Unless you have half a million records or more, I really wouldn't worry about it.
IF you have relationships working, if a username changes, you need only change it ONCE and you're done, so your peers who are panic-mongering are most likely over-reacting to the downsides of using text as a PKF.