UNION ALL

[Samora]

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards

 

[John Nurick]

Hi Samora,

Access (and relational databases in general) don't guarantee to return
records in a consistent order unless you explicitly sort them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the concatenated
field, you'll have to include additional fields in the UNION query.

 

[Samora]

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again

-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't guarantee to return
records in a consistent order unless you explicitly sort them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the concatenated
field, you'll have to include additional fields in the UNION query.

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

Any ordering you do in the constituent queries is lost when you use the
UNION query to combine the results.

The only way I know to do what you want is to include the fields you
want to sort on in the constituent queries and in the UNION query.

If you don't want these additional fields to be included in the query
output, make the UNION query a subquery. Here's an example that works in
the Northwind sample database; it lists contacts in company order for
both Customers and Suppliers.

SELECT ContactAndCompany FROM
(
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Customers
UNION ALL
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Suppliers
)
ORDER BY CompanyName
;

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again

-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't guarantee to return
records in a consistent order unless you explicitly sort them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the concatenated
field, you'll have to include additional fields in the UNION query.

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Randy Harris]

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again



UNION query.

[snip]

John's suggestion is certainly the best solution. You could, however, if
you wished to use a much less elegant solution, insert the data into a
temporary table that contained an autonumber field.

DoCmd.RunSQL "DELETE FROM TEMP_TABLE"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry1"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry2"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry3"

SELECT * from TEMP_TABLE ORDER BY AutoNumberField

would give you all of the data in the order that you wanted.

Clumsy, but it would work.

 

[Samora]

Hi John

Thanks again for your help , but i think i have to clarify
something else.

1 - The Table Header have diferent fields from the table
Detail as from the table TFooter

2 - Of course that my intention to concatenate all the
fields in each table is beacause i have to send them
electronicaly on txt file .In this way i dont have to worry
about

3 - although i have made just 1 field

table Query Field
-------------------------------------------
Theader -> QryExpCab -> CabText
TDetail -> QryExpDet -> DetText
TFooter -> QryExpRod -> RodText

the rsult will go to a Table called TEXPORTAR with a field
named Texto.

When i make the Union All they have to appear in the order
i made the querys. 1st problem: Why don't they appear? I
think you have allready answered that.

However the sugestion you mention i didn't understand quiet
well , so i sent all the details of my Tables , querys and
fields.

4 - As i have to send this TXT.File without any other field
, because there is a record format i have to obey, i
really dont understand how can i do it (By this time i am
exporting the result of to a TXT.File and then i send a
e-mail.( I suppose that in the near i can manage this
problem to make everything go on a command click...but for
now, i still don't have the data well positioned...Let's
give time to time...)

5 - So, how can i take your opinion to solve my problem,
John? Would it be possible for you to work with my tables,
querys and field names in order i can understand all the
point and don't ask again?


I would appreciate very much your opinion .

Thanks for your time

Best regards
Samora

-----Original Message-----
Any ordering you do in the constituent queries is lost when you use the
UNION query to combine the results.

The only way I know to do what you want is to include the fields you
want to sort on in the constituent queries and in the UNION query.

If you don't want these additional fields to be included in the query
output, make the UNION query a subquery. Here's an example that works in
the Northwind sample database; it lists contacts in company order for
both Customers and Suppliers.

SELECT ContactAndCompany FROM
(
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Customers
UNION ALL
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Suppliers
)
ORDER BY CompanyName
;

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again

-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't guarantee to return
records in a consistent order unless you explicitly sort them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the concatenated
field, you'll have to include additional fields in the UNION query.


On Sat, 12 Mar 2005 07:28:59 -0800, "Samora"

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Samora]

Hi Randy

I saw your solution and i think i understand it.

1 - you delete all the records in the temporary table

2 - Insert the records from the the querys where the
concatenated field is allready built

3 - Order the result by an auto number field.


I have to explain one more thing for you and thank for your
help:

I have to send the result by e-mail ,so order the query by
an autonumber field is not a good way, because i have to
order by a Document number that is part of the concatenated
field of the Detail Table.

The solution you sent to me will be done as a command
click, right?

If it is like that , it gives me error with the fields name

i will give all my structure in order you can understand
better my problem:

Tables Querys Field
---------------------------------------
THeader QryExpCab CabText
TDetail QryExpDet DetText
TFooter QryExpRod Rod Text


The field that is mentioned is a concatenated field of all
the fields of the tables that produces the query

After i have the querys, i use the Union All to obtain the
result in my Query name QryTexto.Then i export the result
to a TXT.File to send by e-mail.

the only problem is that : altough i have my Tdetail well
ordered as i want it, when i make the query to obtain the
DetText field and without any changes , just making a field
as an expression as a result of the concatenation, the
order of the table is changed.

And this i don't understand.

Once again i thank for you help and time and would
appreciate very much your help in order to solve this problem.

I think John gave me a good solution but i didn't
understand quiet clear. However i want ot thank you and see
if there is anything else you might see in order to solve
my problem

Thanks again.

Best regards

Samora

-----Original Message-----

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again



UNION query.

[snip]

John's suggestion is certainly the best solution. You could, however, if
you wished to use a much less elegant solution, insert the data into a
temporary table that contained an autonumber field.

DoCmd.RunSQL "DELETE FROM TEMP_TABLE"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry1"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry2"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry3"

SELECT * from TEMP_TABLE ORDER BY AutoNumberField

would give you all of the data in the order that you wanted.

Clumsy, but it would work.

--
Randy Harris
(tech at promail dot com)


.

 

[Randy Harris]

Hi Randy

I saw your solution and i think i understand it.

1 - you delete all the records in the temporary table

2 - Insert the records from the the querys where the
concatenated field is allready built

3 - Order the result by an auto number field.


I have to explain one more thing for you and thank for your
help:

I have to send the result by e-mail ,so order the query by
an autonumber field is not a good way, because i have to
order by a Document number that is part of the concatenated
field of the Detail Table.

The solution you sent to me will be done as a command
click, right?

If it is like that , it gives me error with the fields name

i will give all my structure in order you can understand
better my problem:

Tables Querys Field
---------------------------------------
THeader QryExpCab CabText
TDetail QryExpDet DetText
TFooter QryExpRod Rod Text


The field that is mentioned is a concatenated field of all
the fields of the tables that produces the query

After i have the querys, i use the Union All to obtain the
result in my Query name QryTexto.Then i export the result
to a TXT.File to send by e-mail.

the only problem is that : altough i have my Tdetail well
ordered as i want it, when i make the query to obtain the
DetText field and without any changes , just making a field
as an expression as a result of the concatenation, the
order of the table is changed.

And this i don't understand.

Once again i thank for you help and time and would
appreciate very much your help in order to solve this problem.

I think John gave me a good solution but i didn't
understand quiet clear. However i want ot thank you and see
if there is anything else you might see in order to solve
my problem

Thanks again.

Best regards

Samora

-----Original Message-----

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again



-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't
guarantee to return
records in a consistent order unless you explicitly sort
them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the
concatenated
field, you'll have to include additional fields in the
UNION query.

[snip]

John's suggestion is certainly the best solution. You could, however, if
you wished to use a much less elegant solution, insert the data into a
temporary table that contained an autonumber field.

DoCmd.RunSQL "DELETE FROM TEMP_TABLE"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry1"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry2"
DoCmd.RunSQL "INSERT INTO TEMP_TABLE SELECT * FROM Qry3"

SELECT * from TEMP_TABLE ORDER BY AutoNumberField

would give you all of the data in the order that you wanted.

Clumsy, but it would work.

I think that I must not understand what you are trying to do. I thought
that you wanted the final output to be:

Header Record
Detail Record 1
Detail Record 2
Detail Record 3
....
Detail Record Last
Footer Record

Then send that information to a text file. The order of the detail records
is determined by the document number in your query. Each record - the
Header, the Detail and the Footer is a single concatenated field. Please
explain where I'm wrong.

 

[John Nurick]

Hi Samora,

There are several possibilities.

One is to stick with the UNION query solution. As I understand it, each
of your three constituent queries QryExpCab, QryExpDet and QryExpRod
returns records sorted in the order you want, with a single text field.

In order to preserve the order in the UNION query you need to include
additional fields in the constitutent queries which the UNION query can
use to sort the final output, as in the example in my last post.

To ensure that all the records in the header come before the details
which come before the footer, you can add a calculated field to each of
the queries. For QryExpCab it could be
TableOrder: 1
(in SQL view, this would appear as
1 AS TableOrder
), for QryExpDet
TableOrder: 2
and so on.
If you then sort the UNION query on this field before any others, it
keeps the header, detail and footer in the right order.



Another possibility is Randy's suggestion; there, the Autonumber field
in the temporary table can usually be relied on to keep the records in
the order you expect.



Finally, you can write VBA code that explicitly creates the text file
exactly as required, opening a recordset into QryExpCab and writing its
records to the file, and repeating the process with the two other
queries.

Hi John

Thanks again for your help , but i think i have to clarify
something else.

1 - The Table Header have diferent fields from the table
Detail as from the table TFooter

2 - Of course that my intention to concatenate all the
fields in each table is beacause i have to send them
electronicaly on txt file .In this way i dont have to worry
about

3 - although i have made just 1 field

table Query Field
-------------------------------------------
Theader -> QryExpCab -> CabText
TDetail -> QryExpDet -> DetText
TFooter -> QryExpRod -> RodText

the rsult will go to a Table called TEXPORTAR with a field
named Texto.

When i make the Union All they have to appear in the order
i made the querys. 1st problem: Why don't they appear? I
think you have allready answered that.

However the sugestion you mention i didn't understand quiet
well , so i sent all the details of my Tables , querys and
fields.

4 - As i have to send this TXT.File without any other field
, because there is a record format i have to obey, i
really dont understand how can i do it (By this time i am
exporting the result of to a TXT.File and then i send a
e-mail.( I suppose that in the near i can manage this
problem to make everything go on a command click...but for
now, i still don't have the data well positioned...Let's
give time to time...)

5 - So, how can i take your opinion to solve my problem,
John? Would it be possible for you to work with my tables,
querys and field names in order i can understand all the
point and don't ask again?


I would appreciate very much your opinion .

Thanks for your time

Best regards
Samora

-----Original Message-----
Any ordering you do in the constituent queries is lost when you use the
UNION query to combine the results.

The only way I know to do what you want is to include the fields you
want to sort on in the constituent queries and in the UNION query.

If you don't want these additional fields to be included in the query
output, make the UNION query a subquery. Here's an example that works in
the Northwind sample database; it lists contacts in company order for
both Customers and Suppliers.

SELECT ContactAndCompany FROM
(
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Customers
UNION ALL
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Suppliers
)
ORDER BY CompanyName
;

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again



-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't
guarantee to return
records in a consistent order unless you explicitly sort
them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the
concatenated
field, you'll have to include additional fields in the
UNION query.


On Sat, 12 Mar 2005 07:28:59 -0800, "Samora"

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Samora]

Hi John

according to your last post i have to clarify the following:

Table Header has a field called Type that has "01" value
Table Detail has a field called Type that has "02" value
Table Footer has a field called type that has "03" value

so this guarantee that the Union ALL comes organized
because Type is tre first field and they want it that way
on the format.

In this case i don't think i need any extra field to
guarantee that everything comes organized.

As i explained QryExpDet is based On TDet . This table is
well organized as i want (by field NumDoc).

And i still don't understand why the query that i create
(QryExpDet) before the Union All and that ONLY has one
field (the concatenated one) comes organized as i want and
when i make the Union All the organation is lost.

I understand what you told me about the database. But,
there is any onther solution to this problem i have???

Please forgive me , but i like to learn and make and solve
new challenges...

Hope to hear from you

Best regards

Samora

-----Original Message-----
Hi Samora,

There are several possibilities.

One is to stick with the UNION query solution. As I understand it, each
of your three constituent queries QryExpCab, QryExpDet and QryExpRod
returns records sorted in the order you want, with a single text field.

In order to preserve the order in the UNION query you need to include
additional fields in the constitutent queries which the UNION query can
use to sort the final output, as in the example in my last post.

To ensure that all the records in the header come before the details
which come before the footer, you can add a calculated field to each of
the queries. For QryExpCab it could be
TableOrder: 1
(in SQL view, this would appear as
1 AS TableOrder
), for QryExpDet
TableOrder: 2
and so on.
If you then sort the UNION query on this field before any others, it
keeps the header, detail and footer in the right order.



Another possibility is Randy's suggestion; there, the Autonumber field
in the temporary table can usually be relied on to keep the records in
the order you expect.



Finally, you can write VBA code that explicitly creates the text file
exactly as required, opening a recordset into QryExpCab and writing its
records to the file, and repeating the process with the two other
queries.

Hi John

Thanks again for your help , but i think i have to clarify
something else.

1 - The Table Header have diferent fields from the table
Detail as from the table TFooter

2 - Of course that my intention to concatenate all the
fields in each table is beacause i have to send them
electronicaly on txt file .In this way i dont have to worry
about

3 - although i have made just 1 field

table Query Field
-------------------------------------------
Theader -> QryExpCab -> CabText
TDetail -> QryExpDet -> DetText
TFooter -> QryExpRod -> RodText

the rsult will go to a Table called TEXPORTAR with a field
named Texto.

When i make the Union All they have to appear in the order
i made the querys. 1st problem: Why don't they appear? I
think you have allready answered that.

However the sugestion you mention i didn't understand quiet
well , so i sent all the details of my Tables , querys and
fields.

4 - As i have to send this TXT.File without any other field
, because there is a record format i have to obey, i
really dont understand how can i do it (By this time i am
exporting the result of to a TXT.File and then i send a
e-mail.( I suppose that in the near i can manage this
problem to make everything go on a command click...but for
now, i still don't have the data well positioned...Let's
give time to time...)

5 - So, how can i take your opinion to solve my problem,
John? Would it be possible for you to work with my tables,
querys and field names in order i can understand all the
point and don't ask again?


I would appreciate very much your opinion .

Thanks for your time

Best regards
Samora

-----Original Message-----
Any ordering you do in the constituent queries is lost when you use the
UNION query to combine the results.

The only way I know to do what you want is to include the fields you
want to sort on in the constituent queries and in the UNION query.

If you don't want these additional fields to be included in the query
output, make the UNION query a subquery. Here's an example that works in
the Northwind sample database; it lists contacts in company order for
both Customers and Suppliers.

SELECT ContactAndCompany FROM
(
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Customers
UNION ALL
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Suppliers
)
ORDER BY CompanyName
;


On Sat, 12 Mar 2005 10:29:46 -0800, "Samora"

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again



-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't
guarantee to return
records in a consistent order unless you explicitly sort
them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the
concatenated
field, you'll have to include additional fields in the
UNION query.


On Sat, 12 Mar 2005 07:28:59 -0800, "Samora"

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

If you want to use a UNION query there is NO OTHER SOLUTION than to
include in it the fields on which you want to sort the data. That is the
way SQL works and there is no point discussing it further. Making the
UNION query a subquery , as illustrated in a previous post of mine in
this thread, allows you to still have a query that returns just the one
field.

Hi John

according to your last post i have to clarify the following:

Table Header has a field called Type that has "01" value
Table Detail has a field called Type that has "02" value
Table Footer has a field called type that has "03" value

so this guarantee that the Union ALL comes organized
because Type is tre first field and they want it that way
on the format.

In this case i don't think i need any extra field to
guarantee that everything comes organized.

As i explained QryExpDet is based On TDet . This table is
well organized as i want (by field NumDoc).

And i still don't understand why the query that i create
(QryExpDet) before the Union All and that ONLY has one
field (the concatenated one) comes organized as i want and
when i make the Union All the organation is lost.

I understand what you told me about the database. But,
there is any onther solution to this problem i have???

Please forgive me , but i like to learn and make and solve
new challenges...

Hope to hear from you

Best regards

Samora

-----Original Message-----
Hi Samora,

There are several possibilities.

One is to stick with the UNION query solution. As I understand it, each
of your three constituent queries QryExpCab, QryExpDet and QryExpRod
returns records sorted in the order you want, with a single text field.

In order to preserve the order in the UNION query you need to include
additional fields in the constitutent queries which the UNION query can
use to sort the final output, as in the example in my last post.

To ensure that all the records in the header come before the details
which come before the footer, you can add a calculated field to each of
the queries. For QryExpCab it could be
TableOrder: 1
(in SQL view, this would appear as
1 AS TableOrder
), for QryExpDet
TableOrder: 2
and so on.
If you then sort the UNION query on this field before any others, it
keeps the header, detail and footer in the right order.



Another possibility is Randy's suggestion; there, the Autonumber field
in the temporary table can usually be relied on to keep the records in
the order you expect.



Finally, you can write VBA code that explicitly creates the text file
exactly as required, opening a recordset into QryExpCab and writing its
records to the file, and repeating the process with the two other
queries.

Hi John

Thanks again for your help , but i think i have to clarify
something else.

1 - The Table Header have diferent fields from the table
Detail as from the table TFooter

2 - Of course that my intention to concatenate all the
fields in each table is beacause i have to send them
electronicaly on txt file .In this way i dont have to worry
about

3 - although i have made just 1 field

table Query Field
-------------------------------------------
Theader -> QryExpCab -> CabText
TDetail -> QryExpDet -> DetText
TFooter -> QryExpRod -> RodText

the rsult will go to a Table called TEXPORTAR with a field
named Texto.

When i make the Union All they have to appear in the order
i made the querys. 1st problem: Why don't they appear? I
think you have allready answered that.

However the sugestion you mention i didn't understand quiet
well , so i sent all the details of my Tables , querys and
fields.

4 - As i have to send this TXT.File without any other field
, because there is a record format i have to obey, i
really dont understand how can i do it (By this time i am
exporting the result of to a TXT.File and then i send a
e-mail.( I suppose that in the near i can manage this
problem to make everything go on a command click...but for
now, i still don't have the data well positioned...Let's
give time to time...)

5 - So, how can i take your opinion to solve my problem,
John? Would it be possible for you to work with my tables,
querys and field names in order i can understand all the
point and don't ask again?


I would appreciate very much your opinion .

Thanks for your time

Best regards
Samora
-----Original Message-----
Any ordering you do in the constituent queries is lost
when you use the
UNION query to combine the results.

The only way I know to do what you want is to include the
fields you
want to sort on in the constituent queries and in the
UNION query.

If you don't want these additional fields to be included
in the query
output, make the UNION query a subquery. Here's an example
that works in
the Northwind sample database; it lists contacts in
company order for
both Customers and Suppliers.

SELECT ContactAndCompany FROM
(
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Customers
UNION ALL
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Suppliers
)
ORDER BY CompanyName
;


On Sat, 12 Mar 2005 10:29:46 -0800, "Samora"

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again



-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't
guarantee to return
records in a consistent order unless you explicitly sort
them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the
concatenated
field, you'll have to include additional fields in the
UNION query.


On Sat, 12 Mar 2005 07:28:59 -0800, "Samora"

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Samora]

Hi again John

I understand all the stuff you told me on your previus
thread and the one you mentioned on this reply

However i didn't understand how i am going to do it with my
problem.

If there is any other way to deal with the problem (create
a TXT File that includes all the 3 tables , where the
detail table must be ordered by a special field) please
tell me because i really need that help but i dont want to
spend a lot of time of your own.

Thanks in advance (once again)

Best regards

-----Original Message-----
If you want to use a UNION query there is NO OTHER SOLUTION than to
include in it the fields on which you want to sort the data. That is the
way SQL works and there is no point discussing it further. Making the
UNION query a subquery , as illustrated in a previous post of mine in
this thread, allows you to still have a query that returns just the one
field.

Hi John

according to your last post i have to clarify the following:

Table Header has a field called Type that has "01" value
Table Detail has a field called Type that has "02" value
Table Footer has a field called type that has "03" value

so this guarantee that the Union ALL comes organized
because Type is tre first field and they want it that way
on the format.

In this case i don't think i need any extra field to
guarantee that everything comes organized.

As i explained QryExpDet is based On TDet . This table is
well organized as i want (by field NumDoc).

And i still don't understand why the query that i create
(QryExpDet) before the Union All and that ONLY has one
field (the concatenated one) comes organized as i want and
when i make the Union All the organation is lost.

I understand what you told me about the database. But,
there is any onther solution to this problem i have???

Please forgive me , but i like to learn and make and solve
new challenges...

Hope to hear from you

Best regards

Samora

-----Original Message-----
Hi Samora,

There are several possibilities.

One is to stick with the UNION query solution. As I understand it, each
of your three constituent queries QryExpCab, QryExpDet and QryExpRod
returns records sorted in the order you want, with a single text field.

In order to preserve the order in the UNION query you need to include
additional fields in the constitutent queries which the UNION query can
use to sort the final output, as in the example in my last post.

To ensure that all the records in the header come before the details
which come before the footer, you can add a calculated field to each of
the queries. For QryExpCab it could be
TableOrder: 1
(in SQL view, this would appear as
1 AS TableOrder
), for QryExpDet
TableOrder: 2
and so on.
If you then sort the UNION query on this field before any others, it
keeps the header, detail and footer in the right order.



Another possibility is Randy's suggestion; there, the Autonumber field
in the temporary table can usually be relied on to keep the records in
the order you expect.



Finally, you can write VBA code that explicitly creates the text file
exactly as required, opening a recordset into QryExpCab and writing its
records to the file, and repeating the process with the two other
queries.



On Sun, 13 Mar 2005 07:34:12 -0800, "Samora"

Hi John

Thanks again for your help , but i think i have to clarify
something else.

1 - The Table Header have diferent fields from the table
Detail as from the table TFooter

2 - Of course that my intention to concatenate all the
fields in each table is beacause i have to send them
electronicaly on txt file .In this way i dont have to worry
about

3 - although i have made just 1 field

table Query Field
-------------------------------------------
Theader -> QryExpCab -> CabText
TDetail -> QryExpDet -> DetText
TFooter -> QryExpRod -> RodText

the rsult will go to a Table called TEXPORTAR with a field
named Texto.

When i make the Union All they have to appear in the order
i made the querys. 1st problem: Why don't they appear? I
think you have allready answered that.

However the sugestion you mention i didn't understand quiet
well , so i sent all the details of my Tables , querys and
fields.

4 - As i have to send this TXT.File without any other field
, because there is a record format i have to obey, i
really dont understand how can i do it (By this time i am
exporting the result of to a TXT.File and then i send a
e-mail.( I suppose that in the near i can manage this
problem to make everything go on a command click...but for
now, i still don't have the data well positioned...Let's
give time to time...)

5 - So, how can i take your opinion to solve my problem,
John? Would it be possible for you to work with my tables,
querys and field names in order i can understand all the
point and don't ask again?


I would appreciate very much your opinion .

Thanks for your time

Best regards
Samora
-----Original Message-----
Any ordering you do in the constituent queries is lost
when you use the
UNION query to combine the results.

The only way I know to do what you want is to include the
fields you
want to sort on in the constituent queries and in the
UNION query.

If you don't want these additional fields to be included
in the query
output, make the UNION query a subquery. Here's an example
that works in
the Northwind sample database; it lists contacts in
company order for
both Customers and Suppliers.

SELECT ContactAndCompany FROM
(
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Customers
UNION ALL
SELECT CompanyName,
ContactName & ": " & CompanyName AS ContactAndCompany
FROM Suppliers
)
ORDER BY CompanyName
;


On Sat, 12 Mar 2005 10:29:46 -0800, "Samora"

Thanks for your reply.

1 Question:

When i made the query with 1 single field (concatenation
from various fields of table TDetails) they are allready
sorted in the way i want.

So, why doesn't it come out in that way(sorted the way i
want) ?

I tried to make a ORDER as you say, but as the Query only
have 1 field it becomes sorting the records from left to
right. So, i stay with my problem and don't understand why
having a table allready sorted , when i make a query based
on that table it doesn't come out sorted that way.

i want to remember that my query doesn't have any kind of
order.

my Query has the following design:

SELECT QryHEADER.Text1 FROM QryHEADER
UNION ALL
SELECT QryDetail.Text2 FROM QryDetail
UNION ALL
SELECT QRYFooter.Text3 FROM QryFooter;

i think that with this SQL i would have the way i want it.
But it is not working.

If you have another suggestion i would appreciate very much.

Thanks in advance

Best regards

P.S. How do i make a ORDER BY clause with a query like the
one i mentioned with only one field concatenated ??
When you mention to include aditional fields, i have to
have all 3 tables with the others additionals fields, right??

Please , explain that to me.

Thanks again



-----Original Message-----
Hi Samora,

Access (and relational databases in general) don't
guarantee to return
records in a consistent order unless you explicitly sort
them. You'll
need to include an ORDER BY clause in the UNION query.

If you can't get the result you need by sorting on the
concatenated
field, you'll have to include additional fields in the
UNION query.


On Sat, 12 Mar 2005 07:28:59 -0800, "Samora"

Hello everyone

Something is going wrong on my Union All Query

I have the following problem..

It makes the Union all , but in a order that is not the one
i have in my tables and in my queries.

I have 3 tables and 3 queries.
on the queries i made the concatenation of all fields in
order to have just 1 field to make the union all.

Queries Tables
QryExpCab THeader
QryExpDet TDetail (Indexed by numdoc, dupl allowed)
QryExpRod TFooter

the QryExpDet show me the records ordered by Numdoc

But when i make the Union All it makes me another order
that i can't have it... i have to appear and send tha data
by this order.

I can't understand why this is hapenning...

P.S. TDetail doesn't have any Key, because is just to fill
all the details.

If someone can hel me , i would appreciate very much.

Thanks in advance

Best regards


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Randy Harris]

Write the data to a temp table, then output the content of the table or else
write directly to a text file. Both methods will do exactly what you want.
Why don't you want to use one of those two methods?

 

[Samora]

-----Original Message-----
Write the data to a temp table, then output the content of the table or else
write directly to a text file. Both methods will do exactly what you want.
Why don't you want to use one of those two methods?

--
Randy Harris
(tech at promail dot com)


.

Hi Randy

Thanks for your reply and help

Your sugestion means that i have to use VB Code, right?

But i have some problems as i explained in a previous post.

What happens is when i make the Union All i lose the order
i have in my tables and i don't understand why.

I tried both methods. But in fact, something is going on
that makes the order i have on my querys went wrong.

I am very sorry, but probably i am doing something wrong
but i can't see what.

Anyway thanks for your help. If you have anything else you
can tell me i would appreciate.

Thanks in advance

Best regards

 

[Randy Harris]

Hi Randy

Thanks for your reply and help

Your sugestion means that i have to use VB Code, right?

But i have some problems as i explained in a previous post.

What happens is when i make the Union All i lose the order
i have in my tables and i don't understand why.

Samora,

You are losing the order because you have no ORDER BY clause in your query.
You must accept the fact that the *only* way to control the order in which
records appear in a query is with an ORDER BY clause. It makes no
difference if the query is built from tables or other queries. Tables have
no order. Only queries and only if you use ORDER BY.

As I see it, you have three choices:

1) add fields to your subqueries so that you can use an ORDER BY in the
union query
2) write data to a temp table (if you prefer no code, this could be done
with several successive queries)
3) write the data directly to a file - this would require a slight bit of
code

John has tried very hard to help you. You haven't explained why you can't
use any of these methods. You haven't posted any sample data. You haven't
posted the queries that you are working with. You're not making it easy to
help.

 

[Samora]

Hi Randy

i know that both of you are trying very harder to help me
and i only have to thank you very much to both of you and
also ask for appologies if i can't explain myself

but i will try now:

i have 3 tables (TCab, TDet , TRod ) with lot of fields
that produces querys (QryExpCab , QryExpDet , QryExpRod
respectevely) but with only 1(one field per query) that is
the result of the others fields concatenated in every table.

The querys until now appear ordered the way i want.

But i have a problem . If i add a field in Query
QryExpDet(Detail) i have to add one in QryExpCab and
another in QryExpRod, in order when i make the Union All i
have 2 fields.

so i have to join a field in QryExpCab with the value
"0000" and another one in QryExpRod with the value "9999" ,
so i can have my records ordered by Numdoc.

After that i will make a QUERY based on that Union All but
without the second field that allowed me to order the fields.
And from this query i will export the data to the TXT file
in order to send it by mail.

Am i right with this solution?

Maybe you and John had explained this to me but i didn't
understand quiet clear. Please Forgive me, both of you and
accept my appologies for any inconvenience.

Hope to hear from you

Thanks in advance

Best regards

 

[Randy Harris]

Hi Randy

i know that both of you are trying very harder to help me
and i only have to thank you very much to both of you and
also ask for appologies if i can't explain myself

but i will try now:

i have 3 tables (TCab, TDet , TRod ) with lot of fields
that produces querys (QryExpCab , QryExpDet , QryExpRod
respectevely) but with only 1(one field per query) that is
the result of the others fields concatenated in every table.

The querys until now appear ordered the way i want.

But i have a problem . If i add a field in Query
QryExpDet(Detail) i have to add one in QryExpCab and
another in QryExpRod, in order when i make the Union All i
have 2 fields.

so i have to join a field in QryExpCab with the value
"0000" and another one in QryExpRod with the value "9999" ,
so i can have my records ordered by Numdoc.

After that i will make a QUERY based on that Union All but
without the second field that allowed me to order the fields.
And from this query i will export the data to the TXT file
in order to send it by mail.

Am i right with this solution?

Yes, that is exactly what John recommended. It is probably the best
solution. Without the ORDER BY, you will NOT have control over the order of
the records.

Here is a sample of what such a query might look like:

select Info from (
select QryExpCab.Header as Info, "0000" as Numdoc from QryExpCab
union all
select QryExpDet.Detail as Info, QryExpDet.Numdoc from QryExpDet
union all
select QryExpRod.Footer as Info,"9999" as Numdoc from QryExpRod
) order by Numdoc

This way you will have control over the order of the records. The Detal
records will be in "Numdoc" order.

If this is not what you want, you will have to explain what is wrong with
it. It does not help to simply say it doesn't work. You must explain what
doesn't work.

 

[Samora]

Hi Randy

Finally i learn with the masters..

Thank you very much

Just 3 question.

1 - All that code is on my Query (SQL View) right?

2 - The name of the field in my queries is Texto. So, i
have to replace your Header, Detail and Footer by Texto,
right and stay with "info" right??? Info will be my
information after the union all.

3 - After i make the Query you mentioned , i have to make
another one just with the field info to export to the TXT.File.


Please just answer like that
1-ok
2-no
3Ok for example

No need to loose more time with this problem i guess

Thanks again for your kindness

Best regards

Samora

 

[Samora]

This the 2nd reply after i tried your solution

After made the substituions on the the field names i obtain
an error telling me that there is not a NumDoc on QryExpDet
Query.

I checked twice and it was there.It was the concatenated
field and the NumDoc field in Ascending

There is also something that doesn't match yours
instructions after i save the query

Here it goes ----------------------

My SQL instruction


SELECT info
FROM [select QryExpCab.Texto as Info, "0000" as NumDoc from
QryExpCab
union all
select QryExpDet.Texto as Info, QryExpDet.NumDoc from QryExpDet
union all
select QryExpRod.Texto as Info, "9999" as NumDoc from
QryExpRod]. AS [%$##@_Alias]
ORDER BY Numdoc;


-----------------------------------------------------

As you can see it changes something and returns me an error
on QryExpDet.NumDoc

That i dont know how i am going to do that...

 

[Randy Harris]

Hi Randy

Finally i learn with the masters..

Thank you very much

Just 3 question.

1 - All that code is on my Query (SQL View) right?

Yes. Union queries can't use the Design View, you have to use the SQL View.

2 - The name of the field in my queries is Texto. So, i
have to replace your Header, Detail and Footer by Texto,
right and stay with "info" right??? Info will be my
information after the union all.

Yes and No. I only gave an example. You can use "Info" for output if you
want (it's called an alias). If the field that you want in the output is
already the same in all three queries, you can simply leave it at that.
This would work:

select Texto from (
select QryExpCab.Texto, "0000" as Numdoc from QryExpCab
union all
select QryExpDet.Texto, QryExpDet.Numdoc from QryExpDet
union all
select QryExpRod.Texto, "9999" as Numdoc from QryExpRod
) order by Numdoc

3 - After i make the Query you mentioned , i have to make
another one just with the field info to export to the TXT.File.

No. The query above will return only one field. (Texto) You are using
Numdoc to control the order of the Detail records, but it is not included in
the output. Notice the very first line of the query: "select Texto from ".
It is only selecting the one field. If you were to use "select * from" then
you would get both fields - Texto and Numdoc.

Randy