Combining multiple tables into one table

Odawg

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

Jun 22 '06 #1

Tom van Stiphout

On Thu, 22 Jun 2006 04:06:12 GMT, Odawg <od***@dawgoneloose.com>
wrote:

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,

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

Jun 22 '06 #2

jahoobob via AccessMonster.com

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.

Odawg wrote:

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200606/1

Jun 22 '06 #3

Rich P

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

*** Sent via Developersdex http://www.developersdex.com ***

Jun 23 '06 #4