Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations GregLocock on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

sum of sum of columns 1

Status
Not open for further replies.

flamenco

Computer
Sep 26, 2005
4
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!
 
Replies continue below

Recommended for you

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
 
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;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor