Based on ideas from thread705-760513 I developed a Union All sql query to extract data from four tables all with a common yearid field. I was able to know which table the data came from thanks to an idea from TonyJollans (Programmer) in the above thread to add a marker to each table. It works well.
From this marker I was able to use a group crosstab query to get summary totals for each table in the same query.
A third query (qrytotals) based on the results from the crosstab query gave me the totals for each year for each customer.
Finally, I need to link this query back into the customer table for a form and report. And this is my question. I want the query to return all records from the customers table in a LEFT JOIN on Customers.CustomerID and only those records from the qrytotals where they are equal.
The Select above isn't preforming the left join and I get only records where they are both equal. I am at a loss to know why this is happening.
Regards
John
From this marker I was able to use a group crosstab query to get summary totals for each table in the same query.
A third query (qrytotals) based on the results from the crosstab query gave me the totals for each year for each customer.
Finally, I need to link this query back into the customer table for a form and report. And this is my question. I want the query to return all records from the customers table in a LEFT JOIN on Customers.CustomerID and only those records from the qrytotals where they are equal.
Code:
SELECT qrytotals.Year, qrytotals.GC, qrytotals.MC, qrytotals.PC, qrytotals.SP, qrytotals.AllTotals, Customers.FullAddress, Customers.AllName, Customers.CustomersID, Customers.Area, Customers.DISTRICT
FROM Customers LEFT JOIN qrytotals ON Customers.CustomersID = qrytotals.CustomersID;The Select above isn't preforming the left join and I get only records where they are both equal. I am at a loss to know why this is happening.
Regards
John
