Re: Load an array, sort it, write it to a text file
From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 08/04/04
- Next message: RipperT: "Compile error"
- Previous message: Deb Smith: "Problems with Adding <all> as a parameter"
- In reply to: Chris: "Re: Load an array, sort it, write it to a text file"
- Next in thread: Chris: "Re: Load an array, sort it, write it to a text file"
- Reply: Chris: "Re: Load an array, sort it, write it to a text file"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 3 Aug 2004 21:23:42 -0400
Are you saying that table 1 has 4 fields that total 192 bytes, table 2 has 5
fields that total 192 bytes, table 3 has 2 fields that total 192 bytes and
so on?
SELECT Field1 & Field2 & Field3 & Field4 AS OutputField FROM Table1
UNION
SELECT Field1 & Field2 & Field3 & Field4 & Field5 AS OutputField FROM Table2
UNION
SELECT Field1 & Field2 FROM AS OutputField Table3
ORDER BY 1
Or have I misunderstood?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Chris" <Chris@discussions.microsoft.com> wrote in message news:3967704F-6A0B-4A80-AD42-8A899FFDA05E@microsoft.com... > Hi Doug > > My original idea was that since each line in each of the ten text files are 192 bytes was to create a string array to accomadate the line length, sort the array, and then export it to one text file once it has been sorted. The problem is that the final file has to be in a set format, that each of the ten files takes care of seperately, and once it's all put together as one file it's fine. The final file is then sent to a third party partner that has strict guidelines about the format, so extra fields won't work in this case. Currently, I have to manually cut and paste each line one by one from each of the individual files and paste them into the final file just to get the thing to sort properly. If I can figure out how to load an array from the text files, apply the sort to the array, and then export it all to one text file, I think it would work. > > Any Ideas? > > Thanks > > "Douglas J. Steele" wrote: > > > Pad with blank fields (or null fields) so that each SELECT statement has the > > same number of fields. What were you going to do with the array if there > > weren't the same number of columns for each row? > > > > -- > > Doug Steele, Microsoft Access MVP > > http://I.Am/DougSteele > > (no e-mails, please!) > > > > > > > > "Chris" <Chris@discussions.microsoft.com> wrote in message > > news:291029E3-AC26-4DDE-AF4D-7DD7F495BDF1@microsoft.com... > > > A UNION query will not work in this case because each table has a > > different number of fields. Any other ideas? > > > > > > Thanks > > > Chris > > > > > > "John Vinson" wrote: > > > > > > > On Sun, 1 Aug 2004 11:53:02 -0700, "Chris" > > > > <Chris@discussions.microsoft.com> wrote: > > > > > > > > >Hello, > > > > > > > > > >I have a DB with 10 tables, various fields in each table, each field is > > text data type > > > > >DB also contains 10 queries(one for each table) that I export to a text > > file for each query. Currently I am opening each text file and cutting and > > pasting into one text file, because the end result must be sorted by the > > first two fields in each file and combined into on final file. Each line in > > the text files has a fixed length of 192 bytes. > > > > > > > > > >I want to load each line into an array, sort it, and then write it out > > to the final file. > > > > >Is this the way to go, in this situation or is there a better way? > > Does anyone have any examples? > > > > > > > > > >Thank you. > > > > > > > > I agree with Ken. It's not necessary to write VBA code to do this - > > > > Queries are tools designed to do exactly what you describe. > > > > > > > > For a bit more detail, you can create a UNION query in the SQL window. > > > > Each of the ten queries must have the same number of fields, in the > > > > same order, with matching datatypes (but if you're creating a single > > > > text file that's probably a given). The SQL would be something like > > > > > > > > SELECT ThisField, ThatField, TheOtherField... > > > > FROM FirstQuery > > > > UNION ALL > > > > SELECT AField, AnotherField, SomeField... > > > > FROM SecondQuery > > > > UNION ALL > > > > <etc etc> > > > > SELECT Field1, Field2, Field3, ... > > > > FROM FinalQuery > > > > ORDER BY 1, 3; > > > > > > > > Note that the fieldnames don't need to match between the queries; the > > > > names in the first SELECT clause will be used. If the *last* query has > > > > an ORDER BY clause it will sort the records from the entire query; 1, > > > > 3 means the first and third fields will be used for sorting (i.e. it > > > > will sort in order of ThisField and TheOtherField in my example). > > > > > > > > You can then base an Export on this saved UNION query. > > > > > > > > John W. Vinson[MVP] > > > > Come for live chats every Tuesday and Thursday > > > > http://go.compuserve.com/msdevapps?loc=us&access=public > > > > > > > > > >
- Next message: RipperT: "Compile error"
- Previous message: Deb Smith: "Problems with Adding <all> as a parameter"
- In reply to: Chris: "Re: Load an array, sort it, write it to a text file"
- Next in thread: Chris: "Re: Load an array, sort it, write it to a text file"
- Reply: Chris: "Re: Load an array, sort it, write it to a text file"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
