Combining Query Outputs

[Guest]

Hi,

I have 3 queries that have totally different field outputs.
Once I have run each of these 3 queries, I would like to combine their
output in one file. Their output would be on different lines, the ouput of
query 1 in the first couple of lines, followed by the output of the second
query on the following lines etc. but all included in one file . Is there a
way to do this?

Thanks in advance for your help.

 

[Vincent Johns]

I assume you already include in each Query a field identifying the
record. For example, in Query 1, field [RecordNum] might equal 12345,
and in Query 2, field [RecordNum] for the same record would also equal
12345, etc., and no other record has that same value in the [RecordNum]
field.

In addition, I suggest you add a field, [LineNum], to each Query, with a
value of 1 in every record returned by Query 1, a value of 2 in every
record returned by Query 2, etc.

Then all you need to do is to output all 3 Queries to a file -- it
doesn't matter in what order you do this -- and then sort the file on
those 2 fields. That should give you what you're asking for. It'll
probably be easiest to do this if [RecordNum] is the first field on each
line and [LineNum] immediately follows it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Hi Vincent,

Thanks so much for the reply.
Each of the 3 queries have their own different, specific file layout that I
am currently exporting to 3 different text files. Is there a way to export
each of them and then append them to the same text file?
Also, when I export the data from 2 of the files, the data is wrapped, do
you know if there is a way to avoid this?

Thanks so much for your help Vincent.

 

[Vincent Johns]

Lucy,

To combine them, you could use the command-line commands

copy file1.txt + file2.txt + file3.txt unsorted.txt
sort <unsorted.txt >sorted.txt
del unsorted.txt

to do it, where your 3 files are file1.txt, file2.txt, and file3.txt,
and your final sorted output is to go to the file "sorted.txt".

Be sure to use file names (instead of "unsorted.txt" and "sorted.txt")
that aren't already in use for real files in the folder you're using.

Or you could use Notepad to combine them, if they're not too large, by
opening each one in Notepad and copying the entire contents to another
file in Notepad, then saving the result.

I don't know what you mean by "the data is wrapped". If you're using
Notepad, you can unwrap the lines by clicking on Format and de-checking
the "Word Wrap" option. That won't change the contents of the file, but
will change how they appear in the Notepad window.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Thanks so much Vincent! It works like a charm!
Do you know if there is a way to run this command-line out of access? Eg. by
using a macro?

Thanks again for all your help!

 

[Vincent Johns]

Lucy,

Yes, just put the commands you want ("copy", etc.) into a text file,
save it with an extension of "BAT", such as "Sortem.bat", and invoke it
by writing a Macro with a RunApp action that runs your *.BAT file.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Thanks so much Vincent, you have been a huge help!

Vincent, in relation to one of my previous questions, when I try and run a
macro to export a query, it comes back with the following
error message: "Cannot Update. Database or object is read-only."
I have created macros to run some other queries and they work fine.
There is nothing too different about the query that I am getting the error
message with, except that it does a record count of the output of some other
related queries.
The query only has 1 record as the output, so it is not a big file. It is
actually being used as a trailer record for a file.

I am using the "Open Query" command to run the respective query, which works
fine , but when I use the "Transfer Text" query to export the data, it gives
me the error.

The follwing is what I have in the macro.

I have the Open Query, which works well, but under the action of "Transfer
Text",
I have the following:
Transfer Type: "Export Fixed Width"
Specification Name: "Trailer Record Layout Export Specification"
Table Name: "Trailer Record Layout"
Field Name: "c:\trailer.txt"
Has Field anames: "No"
and the HTML table Name and Code Page are both blank.

Do you have any idea what could be causing this issue?

Thanks so much.

 

[Vincent Johns]

Sorry for the delay -- I've just returned from a trip.

I'm afraid I can't say why you are getting that message. Perhaps it
involves some restriction on the export specification. If so, you can
work around that by defining a Query (based on the [Trailer Record
Layout] Query) that has only one field, one that concatenates all the
fields in [Trailer Record Layout]. Then you could use "Export
Delimited" and not use an export specification.

To see if that might work, you might try defining a Query that returns
only the first field of [Trailer Record Layout], and see if you can
export that, as "Export Delimited", and perhaps avoid the error
condition. If you still get the error message, something else is wrong.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.