This may be a simple question, but I just can't work it out.
I've recently created a new database, to replace the old system which was effectively a flat-file database. Thanks to all who assisted me with that.
The issue now is that I can't work out how to transfer the previous data without loosing data integrity.
-Both the old and the new are in Access 2000
-The old database had no linking. The user would type in the data for one table, record the Key, open the next table, reenter the key, enter the next lot of data.
I need to transfer selected data from selected fields to the new database, in such a way that they will link with each other.
For example: I transfer the classes table, which contains roughtly 300 entries, labled 1-300 by auto-number. The new table also has an auto-number for this field, so I can't copy that field directly, I have to let Access auto-assign new numbers. BUT, this means that they do not match-up with the next table, StudentResults, when I import it, because some records have been deleted over time (leaving gaps in the 1-300).
I hope this makes sense.
The only way I've found (that worked for me) to transfer selected fields only, is to export to an Excel file, and then drag/drop into the new table.
I have the feeling I'm missing something simple, but I havn't had much experience with databases.
In the past when hit with this one Ive
(a) Checked that the new database had working relationships (test it withthe same data taken from the original flat file)
(b) import the flat file table in to the new DB.
(C) make up and append query and set the target records to go to the new table.
what you must make sure of that any references needed for access to find the dataset is placed in each of the new tables.
IE. You may have an autonumber on each record in the flat file, that will have to go in to each table with the relevant records this then will become the ID to ID joins that you will need
I've never had anything to do with append queries, but I'll give it a go.
Just one question though, as I'm not entirely sure how append queries work. When you talk about the OLD autonumber being used as a NEW ID, I have the new database setup with an identical StudentID field, which is also the primary key. If I use an append query, will Access allow me to add the autonumber field from the OLD database to the autonumber field of the NEW database, without changing the actual number? I was under the impresion this was not possible, but I hope to be proved wrong.
Anyway, I'll give it a go when I get the chance, thanks for your reply.
The comment about using the studentID is correct.
If you keep the field names the sane in all the tables that you have previously used youll have little if any prolem tracking what is to go where.
When you set up an append query, append the StudentID in to each table with the data for that table. In doing that you will be able to join the StudantId number in every table to make the relationships you desire.
Ive done it in the past will very little problem yes its time consuming like this but use it as a learning curve as well. As you said you havn't usee append before so give it a go.
if you need more support, shout ill help as much as possible
Ok, just to finish this thread properly, I still havn't had the time to get it all working. I have, however, come to a compromise, and rather than transfer the old data, the new database will start with a clean slate beginning next year.