Reputation: 126
This is a view in SQL Server, why is not working in Access DB?
SELECT
o.IdOrden, o.FechaOrden, o.NumeroDeOrden, p.RazonSocial,
SUM(CASE WHEN r.idtipo = 1 THEN isnull(r.importe, 0) ELSE 0 END) AS [Ch Propio],
SUM(CASE WHEN r.idtipo = 2 THEN isnull(r.importe, 0) ELSE 0 END) AS [Ch Tercero],
SUM(CASE WHEN r.idtipo = 3 THEN isnull(r.importe, 0) ELSE 0 END) AS Efectivo,
SUM(CASE WHEN r.idtipo = 4 THEN isnull(r.importe, 0) ELSE 0 END) AS Retenciones,
SUM(CASE WHEN r.idtipo = 5 THEN isnull(r.importe, 0) ELSE 0 END) AS Transferencias,
SUM(ISNULL(r.Importe, 0)) AS Total,
COUNT(r.Importe) AS [cuenta importe],
COUNT(o.IdOrden) AS [cuenta orden],
o.IdEmpresa, o.idBeneficiario, o.idUsrAlta, o.iAnulado
FROM
dbo.Ordenes AS o
INNER JOIN
dbo.Proveedores AS p ON o.idBeneficiario = p.IdProveedor
INNER JOIN
dbo.Remesas AS r ON o.IdOrden = r.IdOrden
GROUP BY
o.IdOrden, o.FechaOrden, o.NumeroDeOrden, p.RazonSocial,
o.IdEmpresa, o.idBeneficiario, o.idUsrAlta, o.iAnulado
EDIT:
OK, I didn't know Access has so different SQL than SQL Server.
I'm recreating a VB 6.0 windows app connected to SQL Server into Access, not linked to SQL server. Al tables imported well, form are easily develop, but there are 2 views I have to rewrite.
EDIT 2:
This still does not work, but only says Error in JOIN
select
dbo_Ordenes.IdOrden,
dbo_Ordenes.FechaOrden,
dbo_Ordenes.NumeroDeOrden,
dbo_Proveedores.RazonSocial,
sum(iif(dbo_Remesas.idtipo = 1, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Propio],
sum(iif(dbo_Remesas.idtipo = 2, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Tercero],
sum(iif(dbo_Remesas.idtipo = 3, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Efectivo,
sum(iif(dbo_Remesas.idtipo = 4, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Retenciones,
sum(iif(dbo_Remesas.idtipo = 5, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Transferencias,
sum(iif(dbo_Remesas.Importe is null, 0, dbo_Remesas.Importe)) as Total,
count(dbo_Remesas.Importe) as [cuenta importe],
count(dbo_Ordenes.IdOrden) as [cuenta orden],
dbo_Ordenes.IdEmpresa,
dbo_Ordenes.idBeneficiario,
dbo_Ordenes.idUsrAlta,
dbo_Ordenes.iAnulado
from (
dbdbo_Ordenes.Ordenes
inner join dbo_Ordenes.Proveedores
on dbo_Ordenes.idBeneficiario = dbo_Proveedores.IdProveedor
)
inner join dbo_Ordenes.Remesas
on dbo_Ordenes.IdOrden = dbo_Remesas.IdOrden
group by
dbo_Ordenes.IdOrden,
dbo_Ordenes.FechaOrden,
dbo_Ordenes.NumeroDeOrden,
dbo_Proveedores.RazonSocial,
dbo_Ordenes.IdEmpresa,
dbo_Ordenes.idBeneficiario,
dbo_Ordenes.idUsrAlta,
dbo_Ordenes.iAnulado
Upvotes: 0
Views: 72
Reputation: 126
It finally worked. There was a typo.
Somebody recommended this site for SQL translation: https://www.jooq.org/translate/
select
dbo_Ordenes.IdOrden,
dbo_Ordenes.FechaOrden,
dbo_Ordenes.NumeroDeOrden,
dbo_Proveedores.RazonSocial,
sum(iif(dbo_Remesas.idtipo = 1, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Propio],
sum(iif(dbo_Remesas.idtipo = 2, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as [Ch Tercero],
sum(iif(dbo_Remesas.idtipo = 3, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Efectivo,
sum(iif(dbo_Remesas.idtipo = 4, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Retenciones,
sum(iif(dbo_Remesas.idtipo = 5, iif(dbo_Remesas.importe is null, 0, dbo_Remesas.importe), 0)) as Transferencias,
sum(iif(dbo_Remesas.Importe is null, 0, dbo_Remesas.Importe)) as Total,
count(dbo_Remesas.Importe) as [cuenta importe],
count(dbo_Ordenes.IdOrden) as [cuenta orden],
dbo_Ordenes.IdEmpresa,
dbo_Ordenes.idBeneficiario,
dbo_Ordenes.idUsrAlta,
dbo_Ordenes.iAnulado
FROM (dbo_Ordenes INNER JOIN dbo_Proveedores ON dbo_Ordenes.idBeneficiario = dbo_Proveedores.IdProveedor) INNER JOIN dbo_Remesas ON dbo_Ordenes.IdOrden = dbo_Remesas.IdOrden
group by
dbo_Ordenes.IdOrden,
dbo_Ordenes.FechaOrden,
dbo_Ordenes.NumeroDeOrden,
dbo_Proveedores.RazonSocial,
dbo_Ordenes.IdEmpresa,
dbo_Ordenes.idBeneficiario,
dbo_Ordenes.idUsrAlta,
dbo_Ordenes.iAnulado
Upvotes: 1