UNION ALL

[Randy Harris]

Post the SQL for all three queries.

--
Randy Harris
(tech at promail dot com)

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...

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



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.

--
Randy Harris
(tech at promail dot com)



.

 

[Samora]

Hi Randy

Good morning

here are the Querys
--------------------------------------------------------
QryExpCab
---------

SELECT [tipolinha] & [Filer1] & [codentida] & [codtipent] &
[numfact] & [anomesfact] & [dataenvio] & [codrejeicao] &
[filer2] AS Texto
FROM TCab;


QryExpDet
---------

SELECT [TipoLinha] & [NumUniBen] & [Sigla] & [CodCsaude] &
[DtaCSaude] & [Qtd] & [Filer1] & [ValorPagar_Euros] &
[Numdoc] & [CodRej] & [Arealivre] AS Texto
FROM QryDet
ORDER BY QryDet.NumDoc;


QryExpRod
---------

SELECT [TipoLinha] & [QtdTotBen] & [Filer] &
[ValTotal_Euros] & [CodRejeicao] & [Filer2] AS Texto
FROM TRod;

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

Those are the querys i want to make the Union All.

on QryExpCab and on QryExpRod i only retrieve 1 record
(Header and Footer)
On QryExpDet i retrieve a lot of rows where the field
NumDoc can have duplicate rows.

Hope to make more understandble the problem.

Hope to hear from you soon

Thanks in advance

Best regards

-----Original Message-----
Post the SQL for all three queries.

--
Randy Harris
(tech at promail dot com)

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...

-----Original Message-----
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.

--
Randy Harris
(tech at promail dot com)



.

.

 

[Randy Harris]

Samora,

Your QryExpDet query does not output NumDoc. It uses it to order the
records but there is no output. Being part of the concatenated fileld does
not make it available as an output field. Change that query to this:

SELECT [TipoLinha] & [NumUniBen] & [Sigla] & [CodCsaude] &
[DtaCSaude] & [Qtd] & [Filer1] & [ValorPagar_Euros] &
[Numdoc] & [CodRej] & [Arealivre] AS Texto, NumDoc
FROM QryDet

This way NumDoc will be included in the output. There is no point to using
it in the ORDER BY. You will order the records in your union query.

--
Randy Harris
(tech at promail dot com)

Hi Randy

Good morning

here are the Querys
--------------------------------------------------------
QryExpCab
---------

SELECT [tipolinha] & [Filer1] & [codentida] & [codtipent] &
[numfact] & [anomesfact] & [dataenvio] & [codrejeicao] &
[filer2] AS Texto
FROM TCab;


QryExpDet
---------

SELECT [TipoLinha] & [NumUniBen] & [Sigla] & [CodCsaude] &
[DtaCSaude] & [Qtd] & [Filer1] & [ValorPagar_Euros] &
[Numdoc] & [CodRej] & [Arealivre] AS Texto
FROM QryDet
ORDER BY QryDet.NumDoc;


QryExpRod
---------

SELECT [TipoLinha] & [QtdTotBen] & [Filer] &
[ValTotal_Euros] & [CodRejeicao] & [Filer2] AS Texto
FROM TRod;

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

Those are the querys i want to make the Union All.

on QryExpCab and on QryExpRod i only retrieve 1 record
(Header and Footer)
On QryExpDet i retrieve a lot of rows where the field
NumDoc can have duplicate rows.

Hope to make more understandble the problem.

Hope to hear from you soon

Thanks in advance

Best regards

-----Original Message-----
Post the SQL for all three queries.

--
Randy Harris
(tech at promail dot com)

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...


-----Original Message-----
message
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.

--
Randy Harris
(tech at promail dot com)



.

.

 

[John Nurick]

You'll also have to include the NumDoc (ordering) field in the other
queries, e.g.

SELECT [tipolinha] & [Filer1] & [codentida] & [codtipent] &
[numfact] & [anomesfact] & [dataenvio] & [codrejeicao] &
[filer2] AS Texto, 0 AS NumDoc
FROM TCab;

SELECT [TipoLinha] & [QtdTotBen] & [Filer] &
[ValTotal_Euros] & [CodRejeicao] & [Filer2] AS Texto,
999999 AS NumDoc
FROM TRod;

Hi Randy

Good morning

here are the Querys
--------------------------------------------------------
QryExpCab
---------

SELECT [tipolinha] & [Filer1] & [codentida] & [codtipent] &
[numfact] & [anomesfact] & [dataenvio] & [codrejeicao] &
[filer2] AS Texto
FROM TCab;


QryExpDet
---------

SELECT [TipoLinha] & [NumUniBen] & [Sigla] & [CodCsaude] &
[DtaCSaude] & [Qtd] & [Filer1] & [ValorPagar_Euros] &
[Numdoc] & [CodRej] & [Arealivre] AS Texto
FROM QryDet
ORDER BY QryDet.NumDoc;


QryExpRod
---------

SELECT [TipoLinha] & [QtdTotBen] & [Filer] &
[ValTotal_Euros] & [CodRejeicao] & [Filer2] AS Texto
FROM TRod;

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

Those are the querys i want to make the Union All.

on QryExpCab and on QryExpRod i only retrieve 1 record
(Header and Footer)
On QryExpDet i retrieve a lot of rows where the field
NumDoc can have duplicate rows.

Hope to make more understandble the problem.

Hope to hear from you soon

Thanks in advance

Best regards

-----Original Message-----
Post the SQL for all three queries.

--
Randy Harris
(tech at promail dot com)

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...


-----Original Message-----
message
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.

--
Randy Harris
(tech at promail dot com)



.

.

 

[Randy Harris]

Well, he certainly could do it that way, but I had added the fields to the
union query the other day. He would want to do one or the other, but not
both.
--
Randy Harris
(tech at promail dot com)

You'll also have to include the NumDoc (ordering) field in the other
queries, e.g.

SELECT [tipolinha] & [Filer1] & [codentida] & [codtipent] &
[numfact] & [anomesfact] & [dataenvio] & [codrejeicao] &
[filer2] AS Texto, 0 AS NumDoc
FROM TCab;

SELECT [TipoLinha] & [QtdTotBen] & [Filer] &
[ValTotal_Euros] & [CodRejeicao] & [Filer2] AS Texto,
999999 AS NumDoc
FROM TRod;

Hi Randy

Good morning

here are the Querys
--------------------------------------------------------
QryExpCab
---------

SELECT [tipolinha] & [Filer1] & [codentida] & [codtipent] &
[numfact] & [anomesfact] & [dataenvio] & [codrejeicao] &
[filer2] AS Texto
FROM TCab;


QryExpDet
---------

SELECT [TipoLinha] & [NumUniBen] & [Sigla] & [CodCsaude] &
[DtaCSaude] & [Qtd] & [Filer1] & [ValorPagar_Euros] &
[Numdoc] & [CodRej] & [Arealivre] AS Texto
FROM QryDet
ORDER BY QryDet.NumDoc;


QryExpRod
---------

SELECT [TipoLinha] & [QtdTotBen] & [Filer] &
[ValTotal_Euros] & [CodRejeicao] & [Filer2] AS Texto
FROM TRod;

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

Those are the querys i want to make the Union All.

on QryExpCab and on QryExpRod i only retrieve 1 record
(Header and Footer)
On QryExpDet i retrieve a lot of rows where the field
NumDoc can have duplicate rows.

Hope to make more understandble the problem.

Hope to hear from you soon

Thanks in advance

Best regards

-----Original Message-----
Post the SQL for all three queries.

--
Randy Harris
(tech at promail dot com)


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...


-----Original Message-----
message
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.

--
Randy Harris
(tech at promail dot com)



.



.

 

[Samora]

Hi RAndy

Just to say : THANK you

Everything went OK

Best regards

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

Your QryExpDet query does not output NumDoc. It uses it to order the
records but there is no output. Being part of the concatenated fileld does
not make it available as an output field. Change that query to this:

SELECT [TipoLinha] & [NumUniBen] & [Sigla] & [CodCsaude] &
[DtaCSaude] & [Qtd] & [Filer1] & [ValorPagar_Euros] &
[Numdoc] & [CodRej] & [Arealivre] AS Texto, NumDoc
FROM QryDet

This way NumDoc will be included in the output. There is no point to using
it in the ORDER BY. You will order the records in your union query.

--
Randy Harris
(tech at promail dot com)

Hi Randy

Good morning

here are the Querys
--------------------------------------------------------
QryExpCab
---------

SELECT [tipolinha] & [Filer1] & [codentida] & [codtipent] &
[numfact] & [anomesfact] & [dataenvio] & [codrejeicao] &
[filer2] AS Texto
FROM TCab;


QryExpDet
---------

SELECT [TipoLinha] & [NumUniBen] & [Sigla] & [CodCsaude] &
[DtaCSaude] & [Qtd] & [Filer1] & [ValorPagar_Euros] &
[Numdoc] & [CodRej] & [Arealivre] AS Texto
FROM QryDet
ORDER BY QryDet.NumDoc;


QryExpRod
---------

SELECT [TipoLinha] & [QtdTotBen] & [Filer] &
[ValTotal_Euros] & [CodRejeicao] & [Filer2] AS Texto
FROM TRod;

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

Those are the querys i want to make the Union All.

on QryExpCab and on QryExpRod i only retrieve 1 record
(Header and Footer)
On QryExpDet i retrieve a lot of rows where the field
NumDoc can have duplicate rows.

Hope to make more understandble the problem.

Hope to hear from you soon

Thanks in advance

Best regards

-----Original Message-----
Post the SQL for all three queries.

--
Randy Harris
(tech at promail dot com)


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...


-----Original Message-----
message
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.

--
Randy Harris
(tech at promail dot com)



.



.

.