Summarize on different columns and substract

[mauro27]

Hi,



I have a broad understanding of SQL (views, tables, etc.) but I'm a newby in MS Access. My supervisor is asking me to prepare an SQL statement / query in MS Access to get the summarized invoices (in positive amount), credit notes (originally in positive amount) and then the substraction of both for each customer. To give a more precise example, this the original data and the result I would need.

Type 1 = invoice
Type 2 = credit note


Original table: CUSTOMERDATA

Type Customer Amount
1 Cust_A 100
2 Cust_A 8
1 Cust_A 18
1 Cust_B 200
2 Cust_B 35
2 Cust_B 25

Final Report
Customer Invoices_SUM CreditNotes_SUM Total
Cust_A 118 8 110
Cust_B 200 60 240

I was trying to create an SQL view to get this but in my Access version I cannot create VIEWS. What code could I use to get this report?

Thanks in advance,
Mauro.

[pbaldy]

You can Sum a formula:

IIf(TypeField = 1, AmountField, 0)

[ConneXionLost]

Hi Mauro,

An Access query is the equivalent of the SQL View.

SELECT CUSTOMERDATA.Customer, Sum(IIf([Type]=1,[Amount],0)) AS Invoices_SUM, Sum(IIf([Type]=2,[Amount],0)) AS CreditNotes_SUM, [Invoices_SUM]-[CreditNotes_SUM] AS Total
FROM CUSTOMERDATA
GROUP BY CUSTOMERDATA.Customer;

Cheers,

[mauro27]

Hi Mauro,

An Access query is the equivalent of the SQL View.

SELECT CUSTOMERDATA.Customer, Sum(IIf([Type]=1,[Amount],0)) AS Invoices_SUM, Sum(IIf([Type]=2,[Amount],0)) AS CreditNotes_SUM, [Invoices_SUM]-[CreditNotes_SUM] AS Total
FROM CUSTOMERDATA
GROUP BY CUSTOMERDATA.Customer;

Cheers,

Perfect, thanks!