将多个表组合到一个表中

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 getsimple information for my needs. With that said, I was given anopportunity for improvement a database.heres the scenario or process that I am facing1. A total of 3 text files are generated from the mainframe and saveto a secure network share. In each text file only raw dataexample:1. Cus_Name.txt123456789 doe John 071519492. Cus_Address.txt123456789 2100_duncan Austin Tx 787543. Cus_Employer.txt123456789 Imation 01011999 Analyst 2-67892. There is an access database that has 3 tables. One for each txtfile.1. tbl_CusNamefield1: SSNfield2: Last_Namefield3: First_Namefield4: DOB2. tbl_CusAddressfield1: SSNfield2: Addressfield3: Cityfield4: Statefield5: Zip3. tbl_CusEmployerfield1: SSNfield2: Employer_Namefield3: Hire_Datefield4: Titlefield5: Phone3. Each day, each text files are imported into their own designatedtable manually.4. Questions and where i need some assistances:Question 1 -- How can I automate this process so that when I log intothe database, it automatically does the import or insert into therespective tables.Question 2 -- Is there a way for me to either create a query or insertinto some master table all the information from each table so that Ihave one table with all of the field from the other three tables.Question 3 -- The reason for Question 2 is because I will need toexport 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 oneDoCmd.TransferText command. Look it up in the help file.If you name the macro AutoExec, it will automatically execute everytime you open the database, not just once per day.Once you have the macro, say you named it DailyImport, you can set upan automatic process so it executes once a day even if you don''t openthe MDB. Do this on a machine that is always on, always logged in, andhas MsAccess installed. Then use Control Panel > Scheduled Tasks toset up a new task, which executes this command line:path_to_msaccess.exe path_to_your.mdb /x DailyImportand 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 3tables, 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 getsimple information for my needs. With that said, I was given anopportunity for improvement a database.heres the scenario or process that I am facing1. A total of 3 text files are generated from the mainframe and save to a secure network share. In each text file only raw dataexample:1. Cus_Name.txt123456789 doe John 071519492. Cus_Address.txt123456789 2100_duncan Austin Tx 787543. Cus_Employer.txt123456789 Imation 01011999 Analyst 2-67892. There is an access database that has 3 tables. One for each txt file.1. tbl_CusNamefield1: SSNfield2: Last_Namefield3: First_Namefield4: DOB2. tbl_CusAddressfield1: SSNfield2: Addressfield3: Cityfield4: Statefield5: Zip3. tbl_CusEmployerfield1: SSNfield2: Employer_Namefield3: Hire_Datefield4: Titlefield5: Phone3. 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 intothe database, it automatically does the import or insert into therespective tables.Question 2 -- Is there a way for me to either create a query or insertinto some master table all the information from each table so that Ihave one table with all of the field from the other three tables.Question 3 -- The reason for Question 2 is because I will need toexport 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? Ifyou don''t, you should create one for each import. To do this, go through theentire manual import procedure except Finish for each import. When you getto the Finish screen, select Advanced... and the Save as... Give eachimport a different descriptive name.In your macro that Tom has described, enter the appropriate specificationname 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 getsimple information for my needs. With that said, I was given anopportunity for improvement a database.heres the scenario or process that I am facing1. A total of 3 text files are generated from the mainframe and save to a secure network share. In each text file only raw dataexample:1. Cus_Name.txt123456789 doe John 071519492. Cus_Address.txt123456789 2100_duncan Austin Tx 787543. Cus_Employer.txt123456789 Imation 01011999 Analyst 2-67892. There is an access database that has 3 tables. One for each txt file.1. tbl_CusNamefield1: SSNfield2: Last_Namefield3: First_Namefield4: DOB2. tbl_CusAddressfield1: SSNfield2: Addressfield3: Cityfield4: Statefield5: Zip3. tbl_CusEmployerfield1: SSNfield2: Employer_Namefield3: Hire_Datefield4: Titlefield5: Phone3. 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 intothe database, it automatically does the import or insert into therespective tables.Question 2 -- Is there a way for me to either create a query or insertinto some master table all the information from each table so that Ihave one table with all of the field from the other three tables.Question 3 -- The reason for Question 2 is because I will need toexport 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/1Everything you ask for is very doable with some code, and not that muchcode. YOu can automate the importing of data into Access using theDoCmdTransferText method - which requires a specification. You get thespecification by running the textfile import wizard and naming thatimport job. A more efficient way to import textfile data is to use theOpen statement - to open a textfile and then use the input or line inputmethod to read the textfile.One more consideration is if Access is just an intermediate step wherethe final goal is to get the data to Excel. If this is the case you canbypass Access altogether and import the data directly into Excel. Youcan use the import wizard in Excel and use the Record macro function toautomate this, or you can also use VBA to write code using the Openstatement and the input or line input to read the data. The Open andInput or Line Input statements/functions are all in the help files.Rich*** Sent via Developersdex http://www.developersdex.com *** 这篇关于将多个表组合到一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！