Combining 2 queries into one for combobox

[T_Tronix]

I have the following 3 queries, one does one thing, the 2nd one another thing and the 3rd merges both. But when one of the queries returns a null value the merged query seems to give me a null result even though there are values in the second query.



Q1:

SELECT Tasks.Resource_F, Tasks.Resource_LFROM Tasks, CachedDates
WHERE (((Tasks.Start_Date)<[CachedDates].[SDate] Or (Tasks.Start_Date)>[CachedDates].[EDate]) AND ((Tasks.End_Date)<[CachedDates].[SDate] Or (Tasks.End_Date)>[CachedDates].[EDate]));

Q2:

SELECT DISTINCT Employees.First_Name, Employees.Last_NameFROM Employees LEFT JOIN Tasks ON Employees.ACF2_ID = Tasks.Resource_ACF2ID
WHERE (((Tasks.Resource_ACF2ID) Is Null));

Merged Query:

SELECT DISTINCT Employees.First_Name, Employees.Last_NameFROM Employees, Q2_NamesNotInTasks, Q1_AvailableNamesInTasks
WHERE (((Employees.ACF2_ID)=[Q1_AvailableNamesInTasks].[Resource_ACF2ID] Or (Employees.ACF2_ID)=[Q2_NamesNotInTasks].[ACF2_ID])) OR (((Q1_AvailableNamesInTasks.Resource_ACF2ID) Is Null));

[JoeM]

But when one of the queries returns a null value the merged query seems to give me a null result even though there are values in the second query.

Do you mean when one of the queries returns no records, your final merged query returns no records?
That is to be expected, as your merged query has no joins in it. If there are no joins, it returns the Cartesian Product of all the objects (tables, queries) contained in it.
To determine the number of records returned in a Cartesian Product, it is the product (multiplication) of the number of records in each object.
Anything times zero will return zero!

The only way to get records to return in a query where one of the objects has zero records is to do an Outer Join (Left or Right).

Here is some info on Cartesian Products in Access: http://accessdatabasetutorial.com/20...ins-in-access/
There are other articles on it too, if you Google "Access Cartesian Product"

[T_Tronix]

Thanks it worked! Just had to add a relationships in between the tables...