Exporting, cleaning & importing data

[jamiecalvert]

I have built one new Access databse which is intended to replace a number of old databases. The old databases record customers for a salesperson, and orders for each customer. A separate database existed for each salesperson. Everything is combined in the new database (with revised field names).
I would like to upload as much historical data as possible, but the data needs to be 'cleaned'. My plan was to export the historical data into Excel, clean it & then format the headings to correspond with the new database & upload. (I also have to match new Customer ID numbers with Order records. There could be more than one order for each Customer). I have tried this but nothing works. I have tried to find out where the problem is ie add new auto nos, have no auto nos, take out certain colums - but cannot understand why it does not work.
Never tried exporting & uploading before. Should I look at this another way?

 

[The_Doc_Man]

First, it would help to know why "nothing works." Do you get cruddy results or an error message?

Second, don't export to the new database. Import from the old one, one old database at a time. Access likes importing from another Access database. No data conversion errors are possible (unless you run into an Access version problem.)

Import to tablenames that will be treated as strictly temporary tables. Then you can write some append queries based on the local copies of those tables. You can use these queries to populate your REAL tables. And when you are done with each database, just delete the temporary tables associated with it.

Some issues that might trip you...

1. Referential integrity, if present, would require that all parent tables get loaded before any child tables. Otherwise, no import can occur in the child tables.

2. Remember to write your append queries to assert whatever code or name is associated with the old database. Like, if this is Charlie Smith's database, assert Charlie's name or ID as a constant in the appropriate field for the append operation. It is likely to not be there in Charlie's database. After all, Charlie knows who he is - he doesn't need to be reminded. But you do! Or your database does, anyway.

3. Don't import any autonumber field to your new tables. That leads to primary key conflicts, which is one of the possible reasons why your append operation would fail. You will have to let Access re-number the imported entries.

#3 implies that parent-child tables might be a bit harder to import. This is true. You have to import the parent first, then append data to the child tables, with data from the child but the prime key from your new parent table.

 

[jamiecalvert]

Apologies if it had not made things clear. The import action would not complete. I receive an error message. To restate the problem:

New database built. Tables I am concerned with are Customers & Orders

Old databases for each Salesperson exist. As much of the information in these tables needs to be incorporated in the new database.

The fields do differ between databases. For example, the old one has an address & zipcode in one field. The new one has different fields for different parts of the address eg Building name, building no, street or road & so on.

I was planning to export the data from the old database in excel & clean up ie put into field names which correspond with the new database.

I then wanted to import the Excel data into my new databases & the Customer table.

I cannot import as described above and I receive an error message that an error occurred and the file was not imported.
I can however import into a new table into Access. The only thing I presume I would need to do is change the properties for each field to correspond with my Customers table guess delete the old Customers table & rename the recently imported table?

Sorry, I have not come across Append Queries & cannot find any help files on these? Can you provide some more guidance as it sounds like it may help? Reading your reply it sound like it is best to work with Access rather than Excel. This can be done but it will prove considerable more time consuming to clean.

The other reason for using Excel was that once the Customers information is loaded I would need to cross refer the old Customer number with new ones issues so on the order tables they have the correct Customer number shown. These old databases have about 2000 Customers entries in each but are not in any number order ie they may go from 1 to 100 then start at 55,000 & then a batch at 80,000! It would be nice to have historical Customer information having shorter numbers.

Apologies for the length of this reply. I am sure I must be missing some basic things.

 

[The_Doc_Man]

OK, there are two parts to this problem.

First, get the info into Access as temporary tables any way you can. If you have to go through Excel because that's the only way it works, I can understand that. But you want it in Access before you get to part two. You might wish to do this in a copy of your combined database if you have anything in that DB you want to keep already.

Import your data to make one temporary table for customers, another for orders. Use the Tools >> Analyze >> Documenter to get the list of field names once you've done the import. (For thoses cases where you went through Excel, this might be less useful, which is why I said use Access where possible.)

Do the same thing for your permanent customer and order tables. (You only need to analyze the master tables once, though, because their field names won't change.)

You are concerned about adjusting customer numbers. OK, to do this right you need to know the highest customer number currently defined in your combined tables. After each import and append, you need to repeat this step because you are going to be adjusting these tables on the fly. The same consideration applies to any other tables where you have numbers to adjust.

OK, for each database, you will have to import via the append query. So open the query pane of your DB window. Click on NEW. Select the old (temp) table name as your record source. The table's list of fields becomes available in the upper segment of the grid. Close the "Add Table" dialog box. Find the dropdown from the toolbar that will let you change the query from SELECT to APPEND. It will ask you in a new dialog box to name the table to which the append will occur. Name your permanent table.

OK, now you are set. In the top row of the grid, you will select a field from your source table. If a field of the same exact name appears in the destination table, Access will fill it in automatically. If not, you have a source field drop-down and an Append To drop-down as well.

Now, that business about adjusting customer numbers on the fly. Find that highest customer number in the combined table. Say it is something like 231. Pick an arbitrary offset. If your highest number now is 231, pick an offset like 235, 240, or 250. Make it an easy number to remember.

Now, in the customer number field, don't store the customer number. Store an expression of the customer number plus the constant offset. This applies to BOTH customer and order tables. Use the same offset for both.

You might have more than one number to track this way, so don't trust your memory. Write things down. Take notes so you don't confuse yourself instantly. I learned this cold, hard fact a long time ago. Paper has a better memory than people.

OK, when you are done with the data from that partial database, dump the temp tables and start the cycle all over again.