将多个表组合到一个表中 [英] Combining multiple tables into one table
问题描述
Hello All Database（Access）Guru'，
我是数据库的新手，我知道足够了
简单我需要的信息。话虽如此，我获得了改善数据库的机会。
继承了我所面临的情景或过程
1.从大型机生成总共3个文本文件，并将
例如：
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分析师2-6789
2.有一个包含3个表的访问数据库。每个txt一个
文件。
1. tbl_CusName
field1：SSN
field2：Last_Name
field3：First_Name
field4：DOB
2. tbl_CusAddress
field1：SSN
field2 ：地址
field3：City
field4：State
field5：Zip
3. tbl_CusEmployer
field1：SSN
field2：Employer_Name
field3：Hire_Date
field4：title
field5：电话
3.每天，每个文本文件都手动导入到自己指定的
表中。
4.问题以及我需要一些帮助的地方：
问题1 - 我如何自动化这个过程，这样当我登录数据库时，它会自动进行导入或插入
各自的表格。
问题2 - 有没有办法让我创建查询或插入
进入一些主表所有的信息在每张桌子上，这样我就可以在其他三张桌子上找到一张包含所有字段的表格。
问题3 - 问题2的原因是因为我需要
将这些数据导出到ONE（1）Excel电子表格中。
非常感谢任何有关我的问题的帮助。
Argus
2006年6月22日星期四04:06:12 GMT，Odawg< od *** @ dawgoneloose。 com>
写道：
一个简单的方法是使用一个包含3行的宏，每行执行一行
DoCmd。 TransferText命令。在帮助文件中查找。
如果您命名宏AutoExec，它将自动执行您打开数据库的每个
时间，而不是每次执行一次一天。
一旦你有了宏，比如你把它命名为DailyImport，你可以设置
一个自动过程，所以即使你每天执行一次不要打开MDB的
。在始终打开，始终登录的计算机上执行此操作，并且
已安装MsAccess。然后使用控制面板>计划任务
设置一个新任务，执行此命令行：
path_to_msaccess.exe path_to_your.mdb / x DailyImport
和计划它会在第一个用户需要之前每天早上运行。
创建1表查看，创建一个新的选择查询，选择3
表，通过SSN加入它们，然后选择所需的所有字段。
-Tom。
Hello All Database（Access）Guru'，
对于数据库我是一个新手，我知道足够简单
我需要的信息。话虽如此，我获得了改进数据库的机会。
继承了我所面临的情景或过程
1。从大型机生成总共3个文本文件，并保存到安全的网络共享。在每个文本文件中只有原始数据
例如：
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分析员2-6789
2。有一个具有3个表的访问数据库。每个txt
文件一个。
1. tbl_CusName
field1：SSN
field2：Last_Name
field3：First_Name
field4：DOB
2 .tbl_CusAddress
field1：SSN
field2：地址
field3：城市
field4：状态
field5：Zip
3. tbl_CusEmployer
field1： SSN
field2：Employer_Name
field3：Hire_Date
field4：Title
field5：Phone
3。每天，每个文本文件都手动导入到自己指定的表中。
4。问题以及我需要一些帮助的地方：
问题1 - 我如何自动化这个过程，这样当我登录数据库时，它会自动导入或插入到各自的表中问题2 - 有没有办法让我创建查询或在每个表中插入
所有信息，以便我有一张表来自其他三个表的所有字段。
问题3 - 问题2的原因是因为我需要将该数据导出到ONE（1）Excel电子表格中。 br />
非常感谢任何有关我的问题的帮助。
Argus
手动导入它们时，是否使用导入/导出规范？如果你不是b $ b，你应该为每次导入创建一个。为此，请执行
整个手动导入过程，除了每次导入完成。当你在完成屏幕上获得
时，选择高级...并另存为...给每个
导入一个不同的描述性名称。
在Tom描述的宏中，在Action Arguments的第二个框中输入相应的规范
name。
Odawg写道：Hello All Database（Access）Guru'，
我是数据库的新手，我知道足以获得满足我需求的简单信息。话虽如此，我获得了改进数据库的机会。
继承了我所面临的情景或过程
1。从大型机生成总共3个文本文件，并保存到安全的网络共享。在每个文本文件中只有原始数据
例如：
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分析员2-6789
2。有一个具有3个表的访问数据库。每个txt
文件一个。
1. tbl_CusName
field1：SSN
field2：Last_Name
field3：First_Name
field4：DOB
2 .tbl_CusAddress
field1：SSN
field2：地址
field3：城市
field4：状态
field5：Zip
3. tbl_CusEmployer
field1： SSN
field2：Employer_Name
field3：Hire_Date
field4：Title
field5：Phone
3。每天，每个文本文件都手动导入到自己指定的表中。
4。问题以及我需要一些帮助的地方：
问题1 - 我如何自动化这个过程，这样当我登录数据库时，它会自动导入或插入到各自的表中问题2 - 有没有办法让我创建查询或在每个表中插入
所有信息，以便我有一张表来自其他三个表的所有字段。
问题3 - 问题2的原因是因为我需要将该数据导出到ONE（1）Excel电子表格中。
阿格斯
-
通过AccessMonster.com发布的消息
http://www.accessmonster.com/Uwe/For...ccess/200606/1
你要求的一切都是非常可行一些代码，并没有那么多
代码。你可以使用
DoCmdTransferText方法自动导入数据到Access中 - 这需要一个规范。通过运行文本文件导入向导并命名
导入作业，您可以获得
规范。导入文本文件数据的一种更有效的方法是使用
Open语句 - 打开文本文件然后使用输入或行输入
方法来读取文本文件。 br />
还有一个考虑因素是，如果Access只是一个中间步骤，那么最终的目标是将数据传输到Excel。如果是这种情况，您可以完全绕过Access并将数据直接导入Excel。你可以使用Excel中的导入向导并使用Record宏函数来自动执行此操作，或者也可以使用VBA使用Open来编写代码
语句和读取数据的输入或行输入。 Open和
输入或行输入语句/功能都在帮助文件中。
Rich
***通过开发人员指南 http://www.developersdex.com 发送***
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
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
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
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 ***
这篇关于将多个表组合到一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
