Combining multiple tables into one table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Odawg - 22 Jun 2006 05:06 GMT

Hello All Database (Access) Guru's,

I am a novice when it comes to databases and I know enough to get
simple information for my needs. With that said, I was given an
opportunity for improvement a database.

heres the scenario or process that I am facing

1. A total of 3 text files are generated from the mainframe and save
to a secure network share. In each text file only raw data
    example:
    1. Cus_Name.txt
        123456789 doe John 07151949
    2. Cus_Address.txt
        123456789 2100_duncan Austin Tx 78754
    3. Cus_Employer.txt
        123456789 Imation 01011999 Analyst 2-6789

2. There is an access database that has 3 tables. One for each txt
file.
    1. tbl_CusName
        field1: SSN
        field2: Last_Name
        field3: First_Name
        field4: DOB
    2. tbl_CusAddress
        field1: SSN
        field2: Address
        field3: City
        field4: State
        field5: Zip
    3. tbl_CusEmployer
        field1: SSN
        field2: Employer_Name
        field3: Hire_Date
        field4: Title
        field5: Phone

3. Each day, each text files are imported into their own designated
table manually.

4. Questions and where i need some assistances:
Question 1 -- How can I automate this process so that when I log into
the database, it automatically does the import or insert into the
respective tables.

Question 2 -- Is there a way for me to either create a query or insert
into some master table all the information from each table so that I
have one table with all of the field from the other three tables.

Question 3 -- The reason for Question 2 is because I will need to
export that data into ONE (1) Excel spreadsheet.

Any and all help regarding my issue is greatly appreciated.

Argus

Reply to this Message

Tom van Stiphout - 22 Jun 2006 05:16 GMT

A simple way would be to use a Macro with 3 lines, each executing one
DoCmd.TransferText command. Look it up in the help file.

If you name the macro AutoExec, it will automatically execute every
time you open the database, not just once per day.

Once you have the macro, say you named it DailyImport, you can set up
an automatic process so it executes once a day even if you don't open
the MDB. Do this on a machine that is always on, always logged in, and
has MsAccess installed. Then use Control Panel > Scheduled Tasks to
set up a new task, which executes this command line:
path_to_msaccess.exe path_to_your.mdb /x DailyImport
and schedule it to run every morning before the first user needs it.

To create the "1 table" view, create a new Select query, select the 3
tables, join them by SSN, and select all the fields you want.

-Tom.

>Hello All Database (Access) Guru's,
>
[quoted text clipped - 52 lines]
>
>Argus

Reply to this Message

jahoobob - 22 Jun 2006 15:46 GMT

When you import them manually, do you use an Import/Export Specification? If
you don't, you should create one for each import. To do this, go through the
entire manual import procedure except Finish for each import. When you get
to the Finish screen, select Advanced... and the Save as... Give each
import a different descriptive name.
In your macro that Tom has described, enter the appropriate specification
name in the second box of the Action Arguments.

>Hello All Database (Access) Guru's,
>
[quoted text clipped - 52 lines]
>
>Argus

Reply to this Message

Rich P - 23 Jun 2006 18:07 GMT

Everything you ask for is very doable with some code, and not that much
code. YOu can automate the importing of data into Access using the
DoCmdTransferText method - which requires a specification. You get the
specification by running the textfile import wizard and naming that
import job. A more efficient way to import textfile data is to use the
Open statement - to open a textfile and then use the input or line input
method to read the textfile.

One more consideration is if Access is just an intermediate step where
the final goal is to get the data to Excel. If this is the case you can
bypass Access altogether and import the data directly into Excel. You
can use the import wizard in Excel and use the Record macro function to
automate this, or you can also use VBA to write code using the Open
statement and the input or line input to read the data. The Open and
Input or Line Input statements/functions are all in the help files.

Rich

Reply to this Message