UNIONized 2 tables in a Query doesnt allow FilterByForm

[DeoM]

Hi, I made a query to UNION two tables with same structures. One is WorkOrderActive and WorkOrderDone. Both tables have INNER JOIN to a CustomerTable. So in both WorkOrderActive and WorkOrderDone tables, i only have the CustID field. In the WorkOrderForm, it shows all the CustomerTable fields. When not UNIONized, i can run FilterByForm; choose LastName and i can see in a combobox the Name i want to search. But when UNIONized, the LastName combobox only says "Null and IsNotNull"; doesnt show the LastNames.

Here's my code to UNIONized the 2 tables:

SELECT * FROM [2 Customer Info] INNER JOIN [4 Work Order Data (Active Jobs)] ON [2 Customer Info].CustID = [4 Work Order Data (Active Jobs)].CustID UNION ALL SELECT * FROM [2 Customer Info] INNER JOIN [4 Work Order Data (Completed Jobs)] ON [2 Customer Info].CustID = [4 Work Order Data (Completed Jobs)].CustID;

 

[alvechurchdata]

What happens if you UNION Active and Completed jobs and JOIN to customer afterwards?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[DeoM]

After doing a UNION, records from Active and records from the Completed tables are combined; and i can view them in the form. But like i said, both tables have inner join to a CustomerTable. When i do filterbyform on the UNIONed form, the Customer table field name "LastName" shows only "Null and Is Not Null". When not UNIONed, that field, when filterbyform is ran; shows the actual Last Names of the Customers.

 

[alvechurchdata]

After doing a UNION, records from Active and records from the Completed tables are combined

I was suggesting you apply a UNION to WorkOrderActive and WorkOrderDone first so as to get a complete list of CustID and then apply the JOIN to that list. It should give you the same set of results. I don't know why Filter By Form is failing but Access sometimes behaves differently of you force it to do the SQL in a different order.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[DeoM]

Thanks Geoff, I will try that. Access sure makes life worth living; the more you learn the more problems you have. But i like it; very challenging.

 

[DeoM]

Hi Geoff,
Im getting an error if I UNION the 2 tables first then apply INNER JOIN.

 

[PHV]

Im getting an error if I UNION the 2 tables first then apply INNER JOIN
Even if your union is a saved query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244