Combine Multiple tables into One Master 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

 

[Steve Schapel]

Argus,

You can use a macro with 3 TransferText actions, one for each table, to
get the data imported into your Access tables. Not sure which event you
want to use for triggering this process... the Open event of a form
which always opens when the database opens - but then what if today's
import has already been done. Maybe the Click event of a command button.

I think a good approach ten would be to have another table set up, with
all the fields currently included in the three import tables. Make an
Append Query to add the records from one of the imported tables to this
composite table. And then make an Update Query to add the data to the
other fields from the related records in the other two import tables.
The precise details might depend on a few things, for example whether
there is always a 100% matchup of records between the 3 imported files.
Not sure of your requirements - you may also want a Delete Query to
first of all clear out the existing records in the composite table. You
can use OpenQuery actions in your macro to run the Append and Update
queries. And once you've got it working sweet, you might want to add a
SetWarnings/No action before the first OpenQuery, to suppress the
display of the action query confirmation prompts. And then, after all
that, a TransferSpreadsheeet action in the macro to do the Excel export.

Well, that's the general concept, anyway. Hope it makes sense.