Multiple INNER JOIN with GROUP BY and Aggregate Function

Well, you just need to join all the tables required to have the fields needed in the where clause.

And add a where clause.

SELECT t2.Field3,       SUM(t1.Field2) as CTotal,       count(*) as NbItemsFROM Table1 t1 INNER JOIN Table2 t2       ON t1.Field3 = t2.Field1--add a join on Table3INNER JOIN Table3 t3        ON t3.Field1 = t1.Field5 --add your where clauseWHERE t1.Field4 = 'Active'AND t3.Field2 <> 'Pending'GROUP BY t2.Field3--ORDER BY CTotal DESC

see SqlFiddle

EDIT

For your problem

SELCT custInfo.CName, Count(*) as TransactionCount, SUM(CTotal) as TransactionTotal FROM (TamarawTransaction trans INNER JOIN TamarawCustomerInformation custInfo     ON trans.CCustomerCode=custInfo.CCustomerCode) INNER JOIN TamarawTrip  trip    ON trans.CTrip=trip.CTrip Where trip.CStatus='Finalized' AND trans.CStatus='Active' GROUP BY custInfo.CName

With "weird access join syntax"

SELECT TamarawCustomerInformation.CName, Count(*) AS TransactionCount, SUM(CTotal) AS TransactionTotalFROM TamarawCustomerInformationn INNER JOIN (TamarawTrip INNER JOIN TamarawTransaction ON TamarawTrip.CTrip=TamarawTransaction.CTrip)           ON TamarawCustomerInformationn.CCustomerCode=TamarawTransaction.CCustomerCodeWHERE TamarawTrip.CStatus='" & "Shipped" & "' and TamarawTransaction.CStatus='" & "Active" & "'GROUP BY TamarawCustomerInformation.CName;