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:

enter image description here

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







All Articles