Select query is to complex

[Pete Printer]

Hello Folks
I have a form that shows open tasks with date and qualification.
There is a sub form showing available staff and their qualifications.
Staff that are assigned to a task by adding to a many to many table.
The sub form checks that staff are available on that date by checking the availability table and the fact they are not in the booked table on the same date.
The sub form is linked master and child field by date.
There are two values for staff qualification 1 or 2.

The sub form is queried with the below SQL statement which works fine.
It shows staff available by date and qualification.

SELECT qryallStaffAvail.stAvID, qryallStaffAvail.avDate, qryallStaffAvail.STName, tblStaff.qual, qryallStaffAvail.staffId
FROM (qryallStaffAvail LEFT JOIN Booked ON qryallStaffAvail.stAvID = Booked.stAvIDFK) INNER JOIN tblStaff ON qryallStaffAvail.staffId = tblStaff.staffId
WHERE (((tblStaff.qual)=[Forms]![frmBookings]![Qual]) AND ((Booked.stAvIDFK) Is Null));


However staff qualified at 1 can undertake all tasks at both qualification I and qualification 2.
Staff qualified at level 2 can only be assigned to qualification 2 tasks.

I have tried program flow options with IFF and this fails as being too complex.

I hope that i have explained this clearly and welcome any advice or guidance.
Thanks for looking at this
Pete

 

[Minty]

Can you not apply a filter to the sub form based on the current main form record?

 

[arnelgp]

where does it fails, on the Where criteria? you can include the 1's without using IIF:

WHERE (((tblStaff.qual)=1) OR (tblStaff.qual)=[Forms]![frmBookings]![Qual])) AND ((Booked.stAvIDFK) Is Null));

 

[Pete Printer]

Thanks for prompt reply
The query works and the records are filtered with Qualification value 1 or 2.
all fine.

what i would like is if main form 1 filter 1
what i would like is if main form 2 filter 2 OR 1 .

hope this makes sense?

 

[arnelgp]

that is what i gave you, to test:

our criteria:
WHERE (((tblStaff.qual)=1) OR (tblStaff.qual)=[Forms]![frmBookings]![Qual]))


1. textbox Qual: 1

Where (tblStaff.qual=1) Or (tblStaff.qual=1)
'' only returns 1

2. textbox qual: 2

Where (tblStaff.qual=1) Or (tblStaff.qual=2)
'' returns both 1 and 2

 

[Pete Printer]

Arnelgp
Thank you for your excellent solution
and prompt reply.
sorry i was a bit slow to understand :{
Best Regards
Pete

 

[arnelgp]

youre welcome, we somethings overlook the obvious.