I have a Microsoft Access database with nearly 10 years worth of data. Over the years we have taken copies of the database and purged the old data as Access was having trouble with corruption. We have designed a new SQL Server database and would like to move all of our old data into the new database. You job is to:
1. Write a script to move the manifest and sales data from 17 identitical Access DBs into SQL server IN out new format - I will provide SQL DB and diagram
2. Move ALL the data and REMOVE duplicates - the copies of access have duplication
3. Develop a report or two proving that we have properly moved over all the data without duplication or missing some days. (GROUP BY and COUNT on DATE FIELD etc.)
1. The Access database has two main tables which will need to be moved into SQL: Manifest, and Sales.
2. It might be better to create a query connecting a couple of the related foreign keys into the table so you can just have two tables in SQL with the foreign data included.
3. I suggest using SQL Migration Assistant to move these tables and all the data over into SQL, repeat for all 17 databases, and clean up duplicates removing problems.
4. Once you have the data in SQL, do up a quick group by report to see if you have all the data across without duplicates. The data is for a dive boat passenger records and sales. Each day should have between 50-100 passengers. Finding the duplicates should be easy enough.
5. Once you have the SQL data correct, write a SQL script to migrate the data from your simple 2 table SQL server tables and put the data into our new database format. I will provide the schema and a mapping of the data for you. This will involve some manipulation.
6. I will provide all the Access database files by sharing a dropbox folder. Total size 480MB
7. I will be available to answer questions on mapping and database schema. etc.
I have attached an Excel spreadsheet which spells everything out in great detail, including all the mapping between the data, SQL statements of how to move the data from OLD to NEW. Procedure steps showing what steps to take to simplify this project. Please review the Excel spreadsheet for all your job details.
Hi Dave, I'd like to help you on this. Please see your PMB for how I'd approach this to guarantee that the import is being done correctly. regards, J.