CODEWITHSUNDEEP
sql
Giveitagosteve
Giveitagosteve

Reputation: 5

SQL Join and Sum using ACCESS Database

I'm a novice programmer with very limited knowledge of SQL. I've been trying for hours and have read all the resources I can find but can't seem to work out how to join these two tables and get the total invoice value using an ACCESS database.

TblInvoices

CustomerID InvoiceAmount

TblCustomers

CustomerID CustomerName CustomerRep

All I need is the CustomerName, their Rep and the total value of their invoices (OK Between certain dates but I've cracked that bit). I've tried Inner Joins and anything else I can find but without much luck.

As an idea... 

"SELECT CustomerName, CustomerRep, Sum(InvoiceAmount) AS INVOICETOTAL
FROM TblCustomers, TblInvoices WHERE TblCustomers.CustomerID = TblInvoices.CustomerID
GROUP BY CustomerID"

Any help would be gratefully appreciated.

Upvotes: 0

Views: 97

Answers (1)

Taryn
Taryn

Reputation: 247880

You current query is close but since you have CustomerName and CustomerRep in the SELECT you need to GROUP BY those columns:

SELECT TblCustomers.CustomerName, 
  TblCustomers.CustomerRep, 
  Sum(TblInvoices.InvoiceAmount) AS INVOICETOTAL 
FROM TblCustomers
INNER JOIN TblInvoices 
  ON TblCustomers.CustomerID = TblInvoices.CustomerID 
GROUP BY TblCustomers.CustomerName, TblCustomers.CustomerRep;

MS Access requires that any columns appearing in the SELECT list be included in an aggregate function or in the GROUP BY clause when aggregation is being used. If you want to only GROUP BY the CustomerId, then you could also use a subquery to get the result:

SELECT c.CustomerName, 
  c.CustomerRep, 
  i.INVOICETOTAL 
FROM TblCustomers c
INNER JOIN
(
  SELECT Sum(InvoiceAmount) AS INVOICETOTAL,
    CustomerID
  FROM TblInvoices 
  GROUP BY CustomerID
) i
  ON TblCustomers.CustomerID = i.CustomerID

Upvotes: 1

Related Questions