If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create Update Query
I have a number of text files that I have linked to within MS Access. I need
to combine these files into one. All files have the same number of columns and column names. How do I do this? I have started to create an Update Query with all the source tables in the query design window. I have the fields of the destination table in the grid of the query design window. I do not know what information to put into the "Update To" grid of the design window. |
#2
|
|||
|
|||
Create Update Query
Dave,
instead of an update query, use an append query. The first append query will put all the records from the second table into the first table. Create a new append query to put all the records from the third table into the first table. And so on until finished. Jeanette Cunningham -- Melbourne Victoria Australia "Dave" wrote in message ... I have a number of text files that I have linked to within MS Access. I need to combine these files into one. All files have the same number of columns and column names. How do I do this? I have started to create an Update Query with all the source tables in the query design window. I have the fields of the destination table in the grid of the query design window. I do not know what information to put into the "Update To" grid of the design window. |
#3
|
|||
|
|||
Update/Append Query
Am I able to do this within a single query?
|
#4
|
|||
|
|||
Update/Append Query
On Jul 6, 11:54*pm, Dave wrote:
Am I able to do this within a single query? No, you'll need to run an append query against each text file. I wonder if you couldn't create an import specification and then use TransferText. If all the text files are in a single directory, you could just use DIr to loop oover the contents of the directory and process with TransferText and your import spec. (sorry about the typos, I'm not watching what I'm typing...) If you dig around here or in the help there is an example of how to use Dir to loop over the contents of a directory... better yet... check out allen browne's website... www.allenbrowne.com He has an example of a recursive search, among other things. |
#5
|
|||
|
|||
Create Update Query
On Sun, 6 Jul 2008 21:16:00 -0700, Dave
wrote: I have a number of text files that I have linked to within MS Access. I need to combine these files into one. All files have the same number of columns and column names. How do I do this? I have started to create an Update Query with all the source tables in the query design window. I have the fields of the destination table in the grid of the query design window. I do not know what information to put into the "Update To" grid of the design window. An Update query updates existing records in a table, changing the value in the record which is already there... not what you want! Instead, create a local Access table, with all the datatypes and field sizes that you want. Then either do as Jeannette suggests and run multiple Append queries, or you can build a UNION query and do it all in one shot: open a new query based on one of the linked text files, include all the fields, and select View... SQL; then copy and paste the SQL so that it resembles SELECT file1.thisfield, file1.thatfield, file1.theotherfield FROM file1 UNION ALL SELECT file2.thisfield, file2.thatfield, file2.theotherfield FROM file2 UNION ALL etc through all the linked tables Save this query and then base an Append query on it, appending into the new empty Access table. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|