Importing Data

[Guest_imported]

I have a company who sends data to another company with all the data combined into one field. I'm not sure why this is done this way, but this is what I have to work with. I will attach an example of what I am working with.

What I need to do:

1. Take the data from the "Referral_History_Receive" Table.
2. The Document field contains one saved referral. This referral has many fields of data all clumped together in the one document field. <Referral>0 only. Not Read Receipts. Please look closely, the word <Referral> isn't the only data in the Document field. Look at my queries & reports for more samples.
3. The fields clumped together in the Document field is exactly the same structure as the referral table.
4. I need to be able append the contents of the doument field to the referral table. Split up all the data into separate fields.


Thanks. Julie

 

[TimboF]

I have found Excel to be extremely handy for resolving data issues (splitting data elements, search & replace text, combining fields etc). Use it to form a data table that can then be imported into Access. I haven't seen your example, but I hope this helps.
-Tim

 

[nathanstevenson]

This is how to get your data split up and put into the correct tables if you have imported it and it is in one big
table. The best way of doing this is to start from scratch. It also makes creating relationships much easier.

1. Select the table you want to split up.
2. Go to the Tools/Analyse->Table.
3. Follow the steps through the wizard.
4. Select define tables by yourself.
5. You should see a graphical display of the big table, with all your fields contained inside.
6. First make one table by dragging a field out of the first table.
6. Next drag all the fields that you want in that table from the &quot;big&quot; table into the &quot;new&quot; table.
7. Name the new table and define it's relationship.
8. Now to make a 3rd table that has a relationship only with the &quot;new&quot; table you drag all the properties you want in the 3rd table from the &quot;big&quot; table to the &quot;new&quot; table.
9. Next drag the fields out of the &quot;new&quot; table to create the 3rd table.
10. Define the relationships and name the 3rd table.
11. Repeat until you are happy!

- For a full blown application you can add on a couple of frontend forms to your new db to take care of the data entry side.

- The other benefit of this technique is that because of the detail in which Access analyses the discrepancies in data, it brings up an extensive list of &quot;corrections&quot;. This list is very useful when doing a data audit on a large amount of data.

- By doing things in this way, Access also creates a query which can effectively reconstruct your data into it's original format. THis means that you can use that query in forms to make it easier to deal with the data.