Accessing Duplicate SQL Sum Rows
I am trying to write a SQL Access query to get some sub-sampled values ββand sum a bunch of data, but when I run the query the data is duplicated.
Here is my request
SELECT
tt.TransportType,
rp.duns AS Duns,
rp.part AS Part,
rp.plant AS Plant,
rr.Route AS Route,
rr.RouteComp,
tt.TransLength*tt.TransWidth*tt.TransHeight AS [Capacidade],
len(rr.CurrentFrequency) AS [Frequencia],
Capacidade*Frequencia AS Cap_Disp,
s.Cap_Disp*s.FrequenciaTotal AS Cap_Total,
s.FrequenciaTotal,
Cap_Disp/Cap_Total AS Rateio,
FROM ((((tblRoutesParts rp
INNER JOIN tbl20week w ON rp.duns = w.duns
AND rp.part = w.prt
AND rp.plant = w.plant)
INNER JOIN tblRoutesRoutes rr ON rp.Route = rr.Route)
INNER JOIN tblTransportTypes tt ON tt.TransportType = rr.TransportType)
INNER JOIN (SELECT tt.TransportType, rp.duns, rp.part, rp.plant,
sum(len(rr.CurrentFrequency)) as FrequenciaTotal,
sum((tt.TransLength*tt.TransWidth*tt.TransHeight)*(len(rr.CurrentFrequency))) AS Cap_Disp
from ( tblRoutesParts rp
INNER JOIN tblRoutesRoutes rr ON rp.Route = rr.Route)
INNER JOIN tblTransportTypes tt ON tt.TransportType = rr.TransportType
GROUP BY tt.TransportType,
rp.duns,
rp.part,
rp.plant) s ON s.duns= rp.duns
AND s.part = rp.part
AND s.plant = rp.plant)
WHERE left(rp.Route, 1) <> 'L'
and rp.duns = '903323939'
and rp.part = '24584938'
and rp.plant = 'BE'
and rr.Route = 'FRW.A0001'
And here's the output:
As you can see, the data is duplicated only in the amount fields!
Can anyone help me?
+3
source to share
1 answer
Try to join your 's' ON s.TransportType = tt.TransportType. You can get multiple lines when you do an inner join.
SELECT
tt.TransportType,
rp.duns AS Duns,
rp.part AS Part,
rp.plant AS Plant,
rr.Route AS Route,
rr.RouteComp,
tt.TransLength*tt.TransWidth*tt.TransHeight AS [Capacidade],
len(rr.CurrentFrequency) AS [Frequencia],
Capacidade*Frequencia AS Cap_Disp,
s.Cap_Disp*s.FrequenciaTotal AS Cap_Total,
s.FrequenciaTotal,
Cap_Disp/Cap_Total AS Rateio,
FROM ((((tblRoutesParts rp
INNER JOIN tbl20week w ON rp.duns = w.duns
AND rp.part = w.prt
INNER JOIN tblRoutesRoutes rr ON rp.Route = rr.Route)
INNER JOIN tblTransportTypes tt ON tt.TransportType = rr.TransportType)
INNER JOIN (SELECT tt.TransportType, rp.duns, rp.part, rp.plant,
sum(len(rr.CurrentFrequency)) as FrequenciaTotal,
sum((tt.TransLength*tt.TransWidth*tt.TransHeight)*(len(rr.CurrentFrequency))) AS Cap_Disp
from ( tblRoutesParts rp
INNER JOIN tblRoutesRoutes rr ON rp.Route = rr.Route)
INNER JOIN tblTransportTypes tt ON tt.TransportType = rr.TransportType
GROUP BY tt.TransportType,
rp.duns,
rp.part,
rp.plant) s ON s.duns= rp.duns
AND s.part = rp.part
AND s.plant = rp.plant
AND s.TransportType = tt.TransportType)
WHERE left(rp.Route, 1) <> 'L'
and rp.duns = '903323939'
and rp.part = '24584938'
and rp.plant = 'BE'
and rr.Route = 'FRW.A0001'
+2
source to share