combining queries

[WickidWe]

Hi,

I have a working query that displays the outcome based on form values entered.
this is my original query called qryAvailable (sorry its a bit long and messy.)

SELECT DISTINCT tblEmpDetails.EmpId, tblFunctions.FctnDesc, tblEmpDetails.EmpNo, tblEmpDetails.Name, tblFunctions.ReliefCode, tblRelief_Allot.GrRating
FROM (((tblFunctions INNER JOIN ((tbl_shift INNER JOIN tblEmpDetails ON tbl_shift.ShiftCode = tblEmpDetails.Shift) INNER JOIN tblRelief_Allot ON tblEmpDetails.EmpID = tblRelief_Allot.EmpId) ON tblFunctions.ReliefCode = tblRelief_Allot.ReliefCode) INNER JOIN (tblDates INNER JOIN tblRoster ON tblDates.Day_Code = tblRoster.Day_Code) ON (tblEmpDetails.Roster = tblRoster.Roster) AND (tblEmpDetails.Shift = tblRoster.Shift)) INNER JOIN qryEmpLeave ON tblEmpDetails.EmpId = qryEmpLeave.EmpId) INNER JOIN qryNotExempt ON tblEmpDetails.EmpId = qryNotExempt.EmpID
WHERE (((tblEmpDetails.IsVisible)=True) AND ((tblDates.Date_) Like [Forms]![Form1]![txtDate]) AND ((tblEmpDetails.Shift) Like [Forms]![Form1]![cboShift]) AND ((tblRelief_Allot.ReliefCode) Like [Forms]![Form1]![cboFunction]) AND ((tblDates.Day_Name) Like [Forms]![Form1]![txtDay]) AND ((tblEmpDetails.AlternateDuties)=False))
ORDER BY tblEmpDetails.Name;

I have two working queries that I would like to encorporate into the qryAvailable.

overview:
qryAvailable selects the names of people available for specific functions based on the date and the shift that works on that date.
The queries I want to add are for employees who have "Swap Days" (not on there regular shift for selected days)

the following queries work how I need them to on their own, but I am not sure how to put it all together

(shows only the team member that I need to Add to the original qryAvailable list)
qrySwpOn

SELECT tblSwpDay.EmpID, tblFunctions.FctnDesc, tblEmpDetails.EmpNo, tblEmpDetails.Name, tblRelief_Allot.ReliefCode, tblRelief_Allot.GrRating
FROM ((tblSwpDay INNER JOIN tblRelief_Allot ON tblSwpDay.EmpID = tblRelief_Allot.EmpId) INNER JOIN tblFunctions ON tblRelief_Allot.ReliefCode = tblFunctions.ReliefCode) INNER JOIN tblEmpDetails ON tblSwpDay.EmpID = tblEmpDetails.EmpId
WHERE (((tblRelief_Allot.ReliefCode)=[Forms]![Form1]![cboFunction]));

(Shows Everyone on the qryAllocated list, Exept the Person I no longer need)
qrySwpOff

SELECT *
FROM qryAvailable AS QA
WHERE QA.EmpId Not In (SELECT TSD.EmpID
     FROM tblSwpDay AS TSD
     WHERE [Forms]![Form1]![txtDate] = TSD.[New Off]);

I need to show all of (qryAvailable + qrySwpOn) - qrySwpOff



is this even possible?

thanks in advance.

[burrina]

You can combine multiple queries of course. You don't subtract queries, just the results of the query. Queries just like tables need to be joined by like fields unless it is a Cartesian Query.