Sorting problem

D

Diogo

I have a query with a union that works fine, which sorts my records has I
want, nevertheless I wanted to add four totals at the end of the query, in
order to do that I joined them with union all otherwise it would screew up
the sorting if I only used the union clause. But nevertheless it screws up
the same, what I'm I doing wrong:

SELECT Servicos_Diarios_consolidado.Data, Servicos_Diarios_consolidado.[Tipo
de Documento], Servicos_Diarios_consolidado.Nº,
Servicos_Diarios_consolidado.[Tipo de Serviço],
Servicos_Diarios_consolidado.Advogado, Servicos_Diarios_consolidado.Assunto,
Servicos_Diarios_consolidado.Valor, Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por], Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Venda a Dinheiro'
And ((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))

UNION all SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo de Documento],
Servicos_Diarios_consolidado.Nº, Servicos_Diarios_consolidado.[Tipo de
Serviço], Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto, Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por], Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Factura' And
((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))


UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL RECEITAS" as X4, "" as
X5, "" as X6, Sum(Servicos_Diarios_consolidado!Valor) AS SOMATÓRIO1, "" as
X8, "" as X9, "" as X10
FROM Servicos_Diarios_consolidado
WHERE (((Servicos_Diarios_consolidado.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL DESPESAS CLIENTES" as
X4, "" as X5, "" as X6, Sum(Despesas.Valor) AS SOMATÓRIO2, "" as X8, "" as
X9, "" as X10
FROM Despesas
WHERE (((Despesas.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "LUCRO" as X4, "" as X5, ""
as X6, z1!SOMATÓRIO1-z2!SOMATÓRIO2 AS LUCRO, "" as X8, "" as X9, "" as X10
FROM z1, z2

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "NUMERARIO A DEPOSITAR" as
X4, "" as X5, "" as X6, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a
Depositar], "" as X8, "" as X9, "" as X10
FROM z2, z3;


Help please...
 
J

John Spencer

You haven't applied any sorting in the query. What field(s) do you want to
sort by?

You add an order by clause to the last query, using the field names in the
first query. So if you want to sort by Data, you add
ORDER BY Data
By assigning a "" as field values you are forcing all the values to be
treated as a string. I would suggest you use NULL instead

If you wish the totals to appear at the end, then you add a "sort" field to
each query to determine the primary order.

SELECT "A" as GroupOrder, Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "A" , Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "M" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
RECEITAS" as X4
UNION ALL
SELECT "N" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
DESPESAS CLIENTES"
....
UNION ALL
SELECT "Z" as GroupOrder, Null as X1, Null as X2, Null as X3
, "NUMERARIO A DEPOSITAR" as X4, Null as X5, Null as X6
, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a Depositar]
, Null as X8, Null as X9, Null as X10
FROM z2, z3

ORDER BY GroupOrder , Data

That would sort by Group order and then Data

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Diogo said:
I have a query with a union that works fine, which sorts my records has I
want, nevertheless I wanted to add four totals at the end of the query, in
order to do that I joined them with union all otherwise it would screew up
the sorting if I only used the union clause. But nevertheless it screws up
the same, what I'm I doing wrong:

SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo
de Documento], Servicos_Diarios_consolidado.Nº,
Servicos_Diarios_consolidado.[Tipo de Serviço],
Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto,
Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Venda a Dinheiro'
And ((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))

UNION all SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo de Documento],
Servicos_Diarios_consolidado.Nº, Servicos_Diarios_consolidado.[Tipo de
Serviço], Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto, Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Factura' And
((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))


UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL RECEITAS" as X4, ""
as
X5, "" as X6, Sum(Servicos_Diarios_consolidado!Valor) AS SOMATÓRIO1, "" as
X8, "" as X9, "" as X10
FROM Servicos_Diarios_consolidado
WHERE (((Servicos_Diarios_consolidado.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL DESPESAS CLIENTES"
as
X4, "" as X5, "" as X6, Sum(Despesas.Valor) AS SOMATÓRIO2, "" as X8, "" as
X9, "" as X10
FROM Despesas
WHERE (((Despesas.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "LUCRO" as X4, "" as X5, ""
as X6, z1!SOMATÓRIO1-z2!SOMATÓRIO2 AS LUCRO, "" as X8, "" as X9, "" as X10
FROM z1, z2

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "NUMERARIO A DEPOSITAR" as
X4, "" as X5, "" as X6, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a
Depositar], "" as X8, "" as X9, "" as X10
FROM z2, z3;


Help please...
Click to expand...
 
D

Diogo

It works but its sorting data (wich is a date field) like this:


Data
01-05-2007
01-05-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
02-05-2007
02-05-2007
02-05-2007

Not what I expected I want it chronological

John Spencer said:
You haven't applied any sorting in the query. What field(s) do you want to
sort by?

You add an order by clause to the last query, using the field names in the
first query. So if you want to sort by Data, you add
ORDER BY Data
By assigning a "" as field values you are forcing all the values to be
treated as a string. I would suggest you use NULL instead

If you wish the totals to appear at the end, then you add a "sort" field to
each query to determine the primary order.

SELECT "A" as GroupOrder, Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "A" , Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "M" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
RECEITAS" as X4
UNION ALL
SELECT "N" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
DESPESAS CLIENTES"
....
UNION ALL
SELECT "Z" as GroupOrder, Null as X1, Null as X2, Null as X3
, "NUMERARIO A DEPOSITAR" as X4, Null as X5, Null as X6
, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a Depositar]
, Null as X8, Null as X9, Null as X10
FROM z2, z3

ORDER BY GroupOrder , Data

That would sort by Group order and then Data

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Diogo said:
I have a query with a union that works fine, which sorts my records has I
want, nevertheless I wanted to add four totals at the end of the query, in
order to do that I joined them with union all otherwise it would screew up
the sorting if I only used the union clause. But nevertheless it screws up
the same, what I'm I doing wrong:

SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo
de Documento], Servicos_Diarios_consolidado.Nº,
Servicos_Diarios_consolidado.[Tipo de Serviço],
Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto,
Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Venda a Dinheiro'
And ((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))

UNION all SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo de Documento],
Servicos_Diarios_consolidado.Nº, Servicos_Diarios_consolidado.[Tipo de
Serviço], Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto, Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Factura' And
((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))


UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL RECEITAS" as X4, ""
as
X5, "" as X6, Sum(Servicos_Diarios_consolidado!Valor) AS SOMATÓRIO1, "" as
X8, "" as X9, "" as X10
FROM Servicos_Diarios_consolidado
WHERE (((Servicos_Diarios_consolidado.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL DESPESAS CLIENTES"
as
X4, "" as X5, "" as X6, Sum(Despesas.Valor) AS SOMATÓRIO2, "" as X8, "" as
X9, "" as X10
FROM Despesas
WHERE (((Despesas.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "LUCRO" as X4, "" as X5, ""
as X6, z1!SOMATÓRIO1-z2!SOMATÓRIO2 AS LUCRO, "" as X8, "" as X9, "" as X10
FROM z1, z2

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "NUMERARIO A DEPOSITAR" as
X4, "" as X5, "" as X6, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a
Depositar], "" as X8, "" as X9, "" as X10
FROM z2, z3;


Help please...
Click to expand...
Click to expand...
 
J

John Spencer

Did you change the zero-length strings "" to Null as I suggested?

If that doesn't work then try sorting with this order by clause.

ORDER BY IIF(IsDate([Data]), DateValue([Data]),#9999-12-31#)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Diogo said:
It works but its sorting data (wich is a date field) like this:


Data
01-05-2007
01-05-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
02-05-2007
02-05-2007
02-05-2007

Not what I expected I want it chronological

John Spencer said:
You haven't applied any sorting in the query. What field(s) do you want
to
sort by?

You add an order by clause to the last query, using the field names in
the
first query. So if you want to sort by Data, you add
ORDER BY Data
By assigning a "" as field values you are forcing all the values to be
treated as a string. I would suggest you use NULL instead

If you wish the totals to appear at the end, then you add a "sort" field
to
each query to determine the primary order.

SELECT "A" as GroupOrder, Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "A" , Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "M" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
RECEITAS" as X4
UNION ALL
SELECT "N" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
DESPESAS CLIENTES"
....
UNION ALL
SELECT "Z" as GroupOrder, Null as X1, Null as X2, Null as X3
, "NUMERARIO A DEPOSITAR" as X4, Null as X5, Null as X6
, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a Depositar]
, Null as X8, Null as X9, Null as X10
FROM z2, z3

ORDER BY GroupOrder , Data

That would sort by Group order and then Data

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Diogo said:
I have a query with a union that works fine, which sorts my records has
I
want, nevertheless I wanted to add four totals at the end of the query,
in
order to do that I joined them with union all otherwise it would screew
up
the sorting if I only used the union clause. But nevertheless it screws
up
the same, what I'm I doing wrong:

SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo
de Documento], Servicos_Diarios_consolidado.Nº,
Servicos_Diarios_consolidado.[Tipo de Serviço],
Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto,
Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Venda a
Dinheiro'
And ((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))

UNION all SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo de Documento],
Servicos_Diarios_consolidado.Nº, Servicos_Diarios_consolidado.[Tipo de
Serviço], Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto,
Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Factura' And
((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))


UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL RECEITAS" as X4,
""
as
X5, "" as X6, Sum(Servicos_Diarios_consolidado!Valor) AS SOMATÓRIO1, ""
as
X8, "" as X9, "" as X10
FROM Servicos_Diarios_consolidado
WHERE (((Servicos_Diarios_consolidado.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL DESPESAS
CLIENTES"
as
X4, "" as X5, "" as X6, Sum(Despesas.Valor) AS SOMATÓRIO2, "" as X8, ""
as
X9, "" as X10
FROM Despesas
WHERE (((Despesas.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "LUCRO" as X4, "" as X5,
""
as X6, z1!SOMATÓRIO1-z2!SOMATÓRIO2 AS LUCRO, "" as X8, "" as X9, "" as
X10
FROM z1, z2

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "NUMERARIO A DEPOSITAR"
as
X4, "" as X5, "" as X6, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a
Depositar], "" as X8, "" as X9, "" as X10
FROM z2, z3;


Help please...
Click to expand...
Click to expand...
Click to expand...
 
D

Diogo

Friend thanks a lot for tip on the zero-length strings "", that was the
problem.
It's working fine has you wrote it... Thanks.

John Spencer said:
Did you change the zero-length strings "" to Null as I suggested?

If that doesn't work then try sorting with this order by clause.

ORDER BY IIF(IsDate([Data]), DateValue([Data]),#9999-12-31#)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Diogo said:
It works but its sorting data (wich is a date field) like this:


Data
01-05-2007
01-05-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
01-06-2007
02-05-2007
02-05-2007
02-05-2007

Not what I expected I want it chronological

John Spencer said:
You haven't applied any sorting in the query. What field(s) do you want
to
sort by?

You add an order by clause to the last query, using the field names in
the
first query. So if you want to sort by Data, you add
ORDER BY Data
By assigning a "" as field values you are forcing all the values to be
treated as a string. I would suggest you use NULL instead

If you wish the totals to appear at the end, then you add a "sort" field
to
each query to determine the primary order.

SELECT "A" as GroupOrder, Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "A" , Servicos_Diarios_consolidado.Data
....
UNION ALL
SELECT "M" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
RECEITAS" as X4
UNION ALL
SELECT "N" as GroupOrder, Null as X1, Null as X2, Null as X3, "TOTAL
DESPESAS CLIENTES"
....
UNION ALL
SELECT "Z" as GroupOrder, Null as X1, Null as X2, Null as X3
, "NUMERARIO A DEPOSITAR" as X4, Null as X5, Null as X6
, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a Depositar]
, Null as X8, Null as X9, Null as X10
FROM z2, z3

ORDER BY GroupOrder , Data

That would sort by Group order and then Data

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a query with a union that works fine, which sorts my records has
I
want, nevertheless I wanted to add four totals at the end of the query,
in
order to do that I joined them with union all otherwise it would screew
up
the sorting if I only used the union clause. But nevertheless it screws
up
the same, what I'm I doing wrong:

SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo
de Documento], Servicos_Diarios_consolidado.Nº,
Servicos_Diarios_consolidado.[Tipo de Serviço],
Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto,
Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Venda a
Dinheiro'
And ((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))

UNION all SELECT Servicos_Diarios_consolidado.Data,
Servicos_Diarios_consolidado.[Tipo de Documento],
Servicos_Diarios_consolidado.Nº, Servicos_Diarios_consolidado.[Tipo de
Serviço], Servicos_Diarios_consolidado.Advogado,
Servicos_Diarios_consolidado.Assunto,
Servicos_Diarios_consolidado.Valor,
Servicos_Diarios_consolidado_Observações,
Servicos_Diarios_consolidado.[Recebido por],
Servicos_Diarios_consolidado.[ID
Loja]
FROM Servicos_Diarios_consolidado
WHERE (Servicos_Diarios_consolidado.[Tipo de Documento]='Factura' And
((Servicos_Diarios_consolidado.Data) Between
Forms!frmREPORTS_CONSOLIDADO!Data_Inicio And
Forms!frmREPORTS_CONSOLIDADO!Data_Fim))


UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL RECEITAS" as X4,
""
as
X5, "" as X6, Sum(Servicos_Diarios_consolidado!Valor) AS SOMATÓRIO1, ""
as
X8, "" as X9, "" as X10
FROM Servicos_Diarios_consolidado
WHERE (((Servicos_Diarios_consolidado.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "TOTAL DESPESAS
CLIENTES"
as
X4, "" as X5, "" as X6, Sum(Despesas.Valor) AS SOMATÓRIO2, "" as X8, ""
as
X9, "" as X10
FROM Despesas
WHERE (((Despesas.Data) Between
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Inicio] And
[Forms]![frmREPORTS_CONSOLIDADO]![Data_Fim]))

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "LUCRO" as X4, "" as X5,
""
as X6, z1!SOMATÓRIO1-z2!SOMATÓRIO2 AS LUCRO, "" as X8, "" as X9, "" as
X10
FROM z1, z2

UNION ALL SELECT "" as X1, "" as X2, "" as X3, "NUMERARIO A DEPOSITAR"
as
X4, "" as X5, "" as X6, z3!SOMATÓRIO1-z2!SOMATÓRIO2 AS [Numerario a
Depositar], "" as X8, "" as X9, "" as X10
FROM z2, z3;


Help please...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sorting a string array containing combinations 4
Inserting Columns based on missing headers 4
Can I do this? 0
Multi-variable Equation Solving 2
Dynamic Range Using INDEX instead of Offset 4
Excel combinatorics/replace problem 3
Excel Formula Help 3
How do i write this formula in Excel? 3

Top