Please help with nested aggregate query

[Jon-G]

I have the following query, which is producing the results I need in my overview report, but it is EXTREMELY slow! It takes almost 12 seconds to execute on the local machine with is a very high spec brand new quad core machine with 8GB of RAM. This database will be running across a network, with some of the clients being pentium 4 machines so I need to refactor it somehow!

Here is the SQL I have so far for the top level query, which is pulling data from another query which I will add further down:

SELECT 
qryAll_Lote_info.Sector, 
qryAll_Lote_info.Manzana, 
Count(qryAll_Lote_info.Lote_No) AS No_de_lotes, 
DCount("Lote_No","tblLotes","EstadoNo = 1 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Disponible,
DCount("Lote_No","tblLotes","EstadoNo = 2 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Vendido,
DCount("Lote_No","tblLotes","EstadoNo = 3 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Contrato_Privado,
DCount("Lote_No","tblLotes","EstadoNo = 4 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Hipotecado,
DCount("Lote_No","tblLotes","EstadoNo = 5 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'") AS No_Libre,
Sum(qryAll_Lote_info.Lote_Valor) AS Manzana_Valor, 
Sum(qryAll_Lote_info.Total_Pagado_USD) AS Total_Pagado_USD

FROM qryAll_Lote_info
GROUP BY qryAll_Lote_info.Sector, qryAll_Lote_info.Manzana, DCount("Lote_No","tblLotes","EstadoNo = 1 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 2 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 3 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 4 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'"),
DCount("Lote_No","tblLotes","EstadoNo = 5 AND Sector = '" & qryAll_Lote_info.Sector & "' AND Manzana = '" & qryAll_Lote_info.Manzana & "'");

Here is the SQL for the subquery (qryAll_Lote_info):

SELECT tblLotes.Sector, tblLotes.Manzana, tblLotes.Lote_No, tblLotes.Lote_Valor, (SELECT DISTINCT
        SUM(tblPagos.Pago_USD)
      FROM
        tblPagos
      WHERE
    tblLotes.LoteID = tblPagos.LoteID) AS Total_Pagado_USD, tblLotes.EstadoNo
FROM tblLotes;

I really need some help in how to refactor this and I know there MUST be a much better way of pulling all this data together in to one.

TIA
Jon

 

[Jon-G]

bump to the top