sum of sum of columns 1

[flamenco]

Hi!

I'm using access and I have 3 tables: "Customers", "Invoices" and "InvoiceItems"

Invoices
------------------
Folio IdCostum
------------------
1 1
2 3
3 3
4 2
5 1
6 2


Customers
--------------------------------
IdCostum Name
--------------------------------
1 John
2 Steve
3 Joe


InvoiceItems
------------------------------
Folio Item Total
------------------------------
1 anything $10
1 anything $10
2 anything $30
3 anything $7
3 anything $3
4 anything $50
4 anything $20
4 anything $10
5 anything $15
6 anything $15
6 anything $5
6 anything $5

Column "IdCostum" from "Customers" and "IdCostum" from "Invoices" are linked.
Coumn "Folio" from invoices and "Folio" from "InvoiceItems" are linked so the total of each Invoice is equal to the sum of

its items. (for example Invoice "1" is equal to $20, Invoice "2" is equal to $30, etc.)

I want to make a query that returns "IdCostum" and "Name" from "Customers" and the Total of Invoices of each customer like

this

Query
--------------------------------------
IdCostum Name Totals
--------------------------------------
1 John $35
2 Steve $105
3 Joe $40


You notice I need to make a sum of sum of columns

How can I make this query?

Thanks!

 

[rtmpxr]

Here you are:

select customers.idcostum, name, sum(total) as totals from customers
join invoices on customers.idcostum=invoices.idcostum
join invoiceitems on invoiceitems.folio=invoices.folio
group by customers.idcostum, name

 

[pkgupta9]

the above is also correct, but when i use it in microsoft access/sql, it is not working, so the below code is for micorosoft access ....but you should know how to run this code in microsoft access (there must be relationship between tables).

SELECT Customers.IdCustom, name, sum(total) as Total_Sum
FROM (Customers INNER JOIN invoices ON Customers.IdCustom = invoices.IdCustom) INNER JOIN InvoiceItems ON invoices.Folio = InvoiceItems.Folio
group by Customers.IdCustom, name;

 

[flamenco]

thanks pkgupta9!! it works!!