CODEWITHSUNDEEP
sqlms-accessms-access-2010
Luiz Fernando Sousa Camargo
Luiz Fernando Sousa Camargo

Reputation: 335

Access SQL Sum Duplicating Rows

I'm trying to write an Access SQL Query to get some values with a sub-select and sum a bunch of data but when I run the query the data is getting duplicated.

Here´s my query

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

Like you see the data is duplicated only at the sum fields!

Can anyone help me?

Upvotes: 3

Views: 58

Answers (1)

justiceorjustus
justiceorjustus

Reputation: 1965

Try joining your 's' ON s.TransportType = tt.TransportType. You may be getting multiple rows when you inner join s.

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'

Upvotes: 2

Related Questions