I have an Access database for testing which contains two tables, tbvendite and tbpagamenti. Into tbvendite I put every sell concerning the customers, specifying date, Idcliente, cliente, id operation and operation and the amount. Into tbpagamenti I write all payments made by customers that could be the sum of amount grouped by day of sell.

Conditions are that customer can buy in a day but he could not pay, so that he could pay previous suspended amount without making purchases. I should want extract a report for a customer (or a long report) but the query I thought about using does not produce the result I was hoping for.

SELECT tbvendite.data, tbvendite.idcliente, tbvendite.cliente, 
       Sum(iif(tbvendite.importo is null,0,tbvendite.importo)) AS amount, 
       Nz(SS.importo,0) AS PAY
FROM tbvendite 
INNER JOIN (SELECT tbpagamenti.idcliente, 
            Sum(IIf(tbpagamenti.importo Is Null,0,tbpagamenti.importo)) AS Pay 
            FROM tbpagamenti 
            GROUP BY tbpagamenti.idcliente)  AS SS 
   ON tbvendite.idcliente = SS.idCliente
GROUP BY tbvendite.data, tbvendite.idcliente, tbvendite.cliente;

tbvendite

tbpagamenti

current results

CodePudding user response：

One way to do this visually in access would be to

1. create a UNION view which uses both the views created for the tables
2. create a 2nd query with the desired aggregation .
3. run the 2nd query

So step

1. Combine your two tables into 1 using a union and save it as a query in the database named "qryDailySalesPay" for this example.
2. sum and aggregate the information by data, idcliente, cliente.

qryDailySalesPay:

SELECT tbvendite.data
      , tbvendite.idcliente
      , tbvendite.cliente
      , iif(tbvendite.importo is null,0,tbvendite.importo) AS Amount
      , 0 AS PAY
FROM tbvendite
UNION ALL
SELECT tbpagamenti.data
      , tbpagamenti.idcliente
      , tbpagamenti.cliente 
      , 0 as amount
      , IIf(tbpagamenti.importo Is Null,0,tbpagamenti.importo) AS Pay 
 FROM tbpagamenti  

qryDailySalesPaySummary:

SELECT data, ideliente, cliente, sum(amount) as SumAmount, sum(Pay) as SumPay
FROM qryDailySalesPay
GROUP BY data, idcliente, cliente;

MSFT doc on union

Alternate Approach using a derived "DatasALL" table. -- note; you could make this a qrySalesAmountDatas and replace the derived table.

1. Get all dates
2. get all sales which match those dates
3. get all payments whihc match those dates
4. ensure clients match between sales and payments
5. aggregate totals.

.

SELECT tbvendite.data
      , tbvendite.idcliente
      , tbvendite.cliente
      , Sum(iif(tbvendite.importo is null,0,tbvendite.importo)) AS SumAmount
      , Sum(IIf(tbpagamenti.importo Is Null,0,tbpagamenti.importo)) AS SumPay 
FROM (SELECT Distinct data FROM tbvendite 
      UNION 
      SELECT DISTINCT data from tbpagamenti) AS DatasAll
LEFT JOIN tbvendite
 on DatasAll.data = tbvendite.data
LEFT JOIN tbpagamenti 
 on DatasAll.data = tbpagamenti.data
AND tbvendite.idcliente=tbpagamenti.cliente
GROUP BY tbvendite.data
      , tbvendite.idcliente
      , tbvendite.cliente

CodePudding user response：

I think you need to replace Nz (SS.importo, 0) to SS.Pay "importo" does not belong in the subquery (SS)

Regards