Error in using INNER JOIN

[DeoM]

Hi,
In my WorkOrder database, I have 2 tables to UNION (ActiveWorkTable & FinishedWorkTable); both have identical structures. However, each has an INNER JOIN to CustomerTable on "CustID" field.

My SQL is as follows:

SELECT * FROM [ActiveWorkTable] INNER JOIN [CustomerTable] ON [CustomerTable].CustID = [ActiveWorkTable].CustID UNION ALL SELECT * FROM [FinishedWorkTable] INNER JOIN [CustomerTable] ON [CustomerTable].CustID = [FinishedWorkTable].CustID;

When i run WorkOrderForm it shows the combined records of the 2 tables but when i do FilterByForm on say the LastName field (a field of the innerjoined CustomerTable), its say "Null" and "Not Null" instead of showing all the Last Names.
I was advised to UNION the 2 tables first and then INNER JOIN like this:

SELECT * FROM [ActiveWorkTable] UNION ALL SELECT * FROM [FinishedWorkTable] INNER JOIN [CustomerTable] ON [CustomerTable].CustID = [ActiveWorkTable].CustID INNER JOIN [CustomerTable] ON [CustomerTable].CustID = [FinishedWorkTable].CustID;

This generates "Join Error". I tried to put only one INNER JOIN for the ActiveWorkTable...same error message. Seems SQL does not allow an INNER JOIN after a UNION. Any suggestions please?

 

[cmmrfrds]

What happens if you do something like this.


SELECT ActiveWorkTable.*, CustomerTable.* FROM [ActiveWorkTable] INNER JOIN [CustomerTable] ON [CustomerTable].CustID = [ActiveWorkTable].CustID

 

[DeoM]

Thanks Man, Nope, you syntax produced JOIN error. But I already figured out the correct way. I made a UNION query of ActiveWorkTable and FinishedWorkTable; named it AllWorkQuery. I made a form using AllWorkQuery and INNER JOINed CustomerTable to it using the Query Builder (no SQL specific commands). It worked.