A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create Update Query



 
 
Thread Tools Display Modes
  #1  
Old July 7th, 2008, 05:16 AM posted to microsoft.public.access.queries
Dave
external usenet poster
 
Posts: 2,331
Default 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  
Old July 7th, 2008, 05:46 AM posted to microsoft.public.access.queries
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default 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  
Old July 7th, 2008, 05:54 AM posted to microsoft.public.access.queries
Dave
external usenet poster
 
Posts: 2,331
Default Update/Append Query

Am I able to do this within a single query?
  #4  
Old July 7th, 2008, 06:54 AM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 744
Default 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  
Old July 7th, 2008, 07:18 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.