I'm working on an ADM page to see at a snapshot various items.

I'm looking to have total payments and total invoices per year, and thus generate an account balance for the year.

There seems to be errors in both my total payments and my total invoices per year.

It is a little confusing to say the least.

Here is my Total Invoices statement:

ROUND(SUM((o70vm_invoices_items.value)*(o70vm_invoices_items.amount)),2) AS 'INV-TOTAL'

Here is my Total Payments statement:

SUM(o70vm_invoices_payments.payment_amount) AS 'Paid',

Some of the payments and invoices are correct (say 20%). Majority are incorrect.

Here is my complete MySQL query:

$query = "SELECT       

		o70vm_invoices_contacts.name AS 'Parent_Name',

		o70vm_invoices_contacts.id AS 'Parent_ID',

		o70vm_invoices_contacts.company AS 'Children',

		 

		o70vm_invoices_invoices.id AS 'Invoice_ID',

		o70vm_invoices_invoices.user_id AS 'Parent_ID_on_Invoice',

		o70vm_invoices_invoices.invoice_num AS 'Invoice_Num',

		o70vm_invoices_invoices.invoice_date AS 'Invoice_Date',

		o70vm_invoices_invoices.invoice_duedate AS 'Invoice_Date_Due',

		 

		o70vm_invoices_items.id AS 'Item_ID',

		o70vm_invoices_items.invoice_id AS 'Invoice_ID_on_Items',

		o70vm_invoices_items.value AS 'Fee',

		o70vm_invoices_items.amount AS 'Qty',

		

		SUM(o70vm_invoices_payments.payment_amount) AS 'Paid',

		o70vm_invoices_payments.invoice_id,

		o70vm_invoices_payments.payment_status AS 'Payment_Status',

		o70vm_invoices_payments.payment_datetime AS 'Payment_Date',

				

		ROUND(SUM((o70vm_invoices_items.value)*(o70vm_invoices_items.amount)),2) AS 'INV-TOTAL'

				 

		FROM o70vm_invoices_invoices, o70vm_invoices_contacts, o70vm_invoices_items, o70vm_invoices_payments

		WHERE

		o70vm_invoices_contacts.id = o70vm_invoices_invoices.user_id

		AND

		o70vm_invoices_items.invoice_id = o70vm_invoices_invoices.id

		AND

		o70vm_invoices_payments.invoice_id = o70vm_invoices_invoices.id

		AND

		o70vm_invoices_payments.payment_datetime >= STR_TO_DATE('2014-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')

		AND

		o70vm_invoices_payments.payment_datetime <= NOW()

		

		GROUP BY o70vm_invoices_contacts.id";

Any help, very much appreciated.

Ken

PS: Love this component! It is making life much easier.