building query based on smiliar variables with difeerent conditions

  • Thread starter Thread starter Francis
  • Start date Start date
F

Francis

SELECT Empresas_Table.Empresa,
Sum(GestaoTesouraria_Table.valortransaccao) AS totalcashin
FROM Empresas_Table INNER JOIN GestaoTesouraria_Table ON
Empresas_Table.codigoEmpresa=GestaoTesouraria_Table.codigoEmpresa
WHERE (((GestaoTesouraria_Table.IDcashflow) In (1,3,5,7)))
GROUP BY Empresas_Table.Empresa
ORDER BY Empresas_Table.Empresa;

SELECT Empresas_Table.Empresa,
Sum(GestaoTesouraria_Table.valortransaccao) AS cashouttotal
FROM Empresas_Table INNER JOIN GestaoTesouraria_Table ON
Empresas_Table.codigoEmpresa = GestaoTesouraria_Table.codigoEmpresa
WHERE (((GestaoTesouraria_Table.IDcashflow) In (2,4,6,8)))
GROUP BY Empresas_Table.Empresa
ORDER BY Empresas_Table.Empresa;

I want to make a single query that joins these two.

Union query won't work in this case, Inner Join only no either waiting
for suggestions

Thanks advance
 
SELECT Empresas_Table.Empresa,
Sum(IIF(GestaoTesouraria_Table.IDcashflow In
(1,3,5,7),GestaoTesouraria_Table.valortransaccao,null)) AS totalcashin,
Sum(IIF(GestaoTesouraria_Table.IDcashflow In
(2,4,6,8),GestaoTesouraria_Table.valortransaccao,Null)) AS cashouttotal
FROM Empresas_Table INNER JOIN GestaoTesouraria_Table ON
Empresas_Table.codigoEmpresa=GestaoTesouraria_Table.codigoEmpresa
WHERE (((GestaoTesouraria_Table.IDcashflow) In (1,3,5,7,2,4,6,8)))
GROUP BY Empresas_Table.Empresa
ORDER BY Empresas_Table.Empresa;
 
Back
Top