Database Design and importing data?

[ekta]

Hi:

I have been asked to work on a database. I need some advice on how I should go about it. I have these text files which contain data and I need to import it in Access tables. The data looks like this:

ACY, 0001, SUBCER1A, 1, N, 11
ACY, 0001, SUBCER1A, 2, N, 13
ACY, 0001, SUBCER1A, 3, N, 14
ACY, 0001, SUBCER1A, 4, Y, 7

ACY is the airport, 0001 is the inspector number who enters these records. I have separate text files like these for different inspectors. SUBCER1A is the test, then image #, Y/N is the test failed or not and last column is the time in seconds. I am guessing that I need to create 2 tables. One for inspector number and the other for the fields above, with a one-to-many relationship between them.
The other thing I am having trouble with is how should I import this data in the tables? Each text file has hundreds and hundreds of records. What is the easiest way of doing this? Is it possible to automate it?

I would really appreciate any advise.

Thanks,

Ekta

 

[gmlwong]

How's this?...

EKTA:

Create separate tables for the airports, the inspectors, and the varous tests.
Set primary keys (1-to-Many) on each to maintain referential integrity; (i.e. [tblAirports] would have these two fields; [AirportID] and [AirportName] as the PK; follow the same for the other tables as well.

In your main table, include the "ID" field names from the other separate tables and set the row source for these fields to equal their counterparts; so, [AirportID] in your main table would have a row source = [tblAirports].[AirportID]; your main table's properties window will assist you in the proper syntax.

Set the following data types for you main table: [AirportID] = Autonumber, [InspectorID] = Autonumber, [TestID] = Autonumber, [Image] = Number, [Failed?] = Yes/No box, [Passed?] = Yes/No box, and [Time] = Number (seconds). ASAP...complete your table relationships now or you'll have many problems!

Remember, Access doesn't like it if you alter data types once you have entered data so KNOW that this data is and will forever be what they are; (i.e. changing from text to number can result in a massive loss of data throughout your recordset!) If a fields data is a number, it has to be forever, to avoid irregularities.

As far as your import process, once you have your Access table structure set and relationships are defined, the imports are easy. From a text file type, just copy and paste all of the text records into Excel and name your columns EXACTLY after the names that live in your main table. If the text file has label names, these should "pop" for you in Excel, just make sure that you re-name them to match your Access table field names...this will save you a lot of time on the next step...

Next, it helps to format your new Excel file columns so that they match as closely to their Access table counterparts; Text to Text, Number to Number, etc...mixing these, again can cause a loss of data.

Now, using the "Getting External Data" function in Access, call on this Excel file and import the recordset into it's own table in your Access database.

Then, create an Append query to add these records into your main table; weekly, monthly, whatever. Make sure there are no duplicates records. To check for this after the import, just create a "Find Duplicates" query and Access will find these duplicates for you. If you've mimicked the fields names, each will match when you begin your Append query process. If they don't select the field that matches the data you're trying to import (append).

I know there are ways to automate this process but I don't know Oracle8/8i or other applications to create the queries that would be required to do this.

I perform the above steps many times each week with the databases I manage and I enjoy it because it enhances and improves my use of Access and my new VBA skills...as you do the same, you'll pick up on a lot of great hints and formula writing that will help your import processes even more!

Read through some of these topics in this forum because there is a lot of great advice out there from many sharp people! - Mr. Hartman, Vassago, Mile-O-Phile, ghudson, Idjit, and many more.

I hope I've helped you out here...

Hi:

I have been asked to work on a database. I need some advice on how I should go about it. I have these text files which contain data and I need to import it in Access tables. The data looks like this:

ACY, 0001, SUBCER1A, 1, N, 11
ACY, 0001, SUBCER1A, 2, N, 13
ACY, 0001, SUBCER1A, 3, N, 14
ACY, 0001, SUBCER1A, 4, Y, 7

ACY is the airport, 0001 is the inspector number who enters these records. I have separate text files like these for different inspectors. SUBCER1A is the test, then image #, Y/N is the test failed or not and last column is the time in seconds. I am guessing that I need to create 2 tables. One for inspector number and the other for the fields above, with a one-to-many relationship between them.
The other thing I am having trouble with is how should I import this data in the tables? Each text file has hundreds and hundreds of records. What is the easiest way of doing this? Is it possible to automate it?

I would really appreciate any advise.

Thanks,

Ekta

 

[ekta]

Thanks so much for replying gmlwong. I tried your suggestion and created normalised tables. I am still not sure how I should import data in the main table. I also created another db with just one table with all the required fields and imported the data in it from the text files. It worked just fine. My question is that because I just need to import data in access table, is it necessary to normalise it? Another question I have is that why do I have to copy the data in an excel spreadsheet first and then in access table. I directly imported the data in the non-normalised database and it worked fine.
I am attaching both the dbs and will really appreciate your further advice.

Thanks,

Ekta

 

[gmlwong]

Great!...a couple things I noticed...

Anytime you're importing a large amount of data, especially if it resides in a text type file, I think my fellow developers would agree, copying these records into an Excel Worksheet makes it easier to catch errors and to set data types & changes that are more difficult to make in a text file; I manage (4) very large text files every Wednesday for one of my main mdb's and I simply don't trust the output for importing these records directly into my mdb, so I take the extra time (doesn't take much but well worth it) to copy/paste into Excel so that I can treat each column of data and set data types correctly so that my import into Access runs smoothly and has no or very little data type losses or errors...of course it depends on what you are doing with all this data as well as far as how you want to report on it, who sees it and how, etc...

My databases are used over a network of 12-14 users every day and I wouldn't trust a more basic style of import (straight text file) for that data because it would definitely tie up everyone's time in checking and making changes to the data when I can use Excel to help manage these issues and eliminate those extra steps and waste of time.

This doesn't apply to Oracle or VB developers who can write scripts to extract and import any piece of data they choose; I'm not at this level so I deal the best and safest way I can with what I know...

In 15years+ of data configuration, database development and report creation and management, I think it's very safe to say and to have the belief that you always want to make your applications and record processing as easy for the users as you can because it will always save you time in the long run!

I looked at both files and it looks like you have a good start. I would just suggest that you dig a little deeper into how extensive this database and it's data will be and decide from there how robust or simple you'd like it to be. Check your data types and relationships. I noticed many missing or incorrect settings...

If it's always going to be simple, absolutely, keep it simple but if it is going to be used by many people and complex queries for forms and reports may be requested, I'd really spruce it up!

Thanks so much for replying gmlwong. I tried your suggestion and created normalised tables. I am still not sure how I should import data in the main table. I also created another db with just one table with all the required fields and imported the data in it from the text files. It worked just fine. My question is that because I just need to import data in access table, is it necessary to normalise it? Another question I have is that why do I have to copy the data in an excel spreadsheet first and then in access table. I directly imported the data in the non-normalised database and it worked fine.
I am attaching both the dbs and will really appreciate your further advice.

Thanks,

Ekta

 

[ekta]

Thanks for your valuable advice. I really appreciate it. I was told by the user that I just need to import the data from the text files in the access tables. It won't be used for anything else. But I am going to talk to the user once more and confirm that he won't ask me to create report, queries in the future. If that's the case then I will normalize the tables.

I will get back to you if I have any more questions.

Thanks again,

Ekta