I am trying to generate a set of data sums from multiple tables based on a common set of parameters (dates) as a single row with the first column being one of the parameters. Typically, this would be for a day, a week, a month, ... and would be regularly run adding the row to the end of an excel file. Typical data would be Sales Book, Sales Bill, PO issued, materials received, etc
At the moment I am strugling with the first part - just getting started on the row of data up to the UNION ALL.
SELECT 'EndDate' AS Caption, SUM(SOPInvoiceCreditLine.LineTotalValue) AS InvSum FROM SOPInvoiceCreditLine InvoiceCreditLine WHERE SOPInvoiceCreditLine.InvoiceCreditDate>= [StartDate] and SOPInvoiceCreditLine.InvoiceCreditDate <=[EndDate] UNION ALL SELECT Sum(SOPOrderReturnLine.LineTotalValue) AS OrdSum WHERE SOPOrderReturnLine.InvoiceCreditDate>= [StartDate] and SOPOrderReturnLine.InvoiceCreditDate <=[EndDate]
The error message says "The multi-part identifier " SOPInvoiceCreditLine.InvoiceCreditDate" could not be bound", and similarly for LineTotalValue.
In the long term, there would be data from 4 - 10 tables as columns after the data.