Query only unused items

[poporacer]

I have a db with a table for staff (tblStaff) and a table for incidents (tblIncident) I have a form for creating a new incident. After you create the incident, you can add a staff member(s) that were involved in the incident. What I want is a combobox that has the list of staff in tblStaff but NOT already in the incident. I figured that I need a loin query with a way to link to the Incident table. I have tried some things and am close but not quite there here is a simplified version to get the point accross. I have a function that contains a global variable to indicate what the current incident log number is ( getCurrentIncidentNum() )

SELECT * from tblStaff where tblStaff.id <> tblIncident.StaffId leeft join on tblIncident.incident= getCurrentIncidentNum
I am not sure exactly how to do this query...any ideas?

 

[ByteMyzer]

SELECT T1.*
FROM tblStaff T1
LEFT JOIN tblIncident T2 ON T1.id = T2.StaffId
WHERE T2.StaffId Is Null;

 

[poporacer]

hmm. It didn't seem to work the table came back blank I did edit the query a bit as it seemed a little off. Here is what I used
SELECT T1.*
FROM tblStaff T1
LEFT JOIN tblIncident T2 ON T1.id = T2.Id
WHERE T2.Id Is Null;

Because the fields were labled tblStaff.Id and tblIncidents.Id.
I had a typo on the first post

 

[WayneRyan]

poporacer,

Surely you don't have all of the Staff that participated in the incident in the
table tblIncident. You must have one more table.

Wayne

 

[poporacer]

Sorry,
I have a table (tblStaff)that is a master table of all staff, and a table (tblIncident) that is a master table of all incidents. And then a table (tblIncidentStaff) that has an entry for each Staff involved in an incident with the particulars of the incident.

 

[WayneRyan]

poporacer,

I think this is it.

SELECT A.StaffName
FROM   tblStaff As A Left Join tblIncidentStaff As B On
         A.StaffID = B.StaffID 
Where  B.IncidentID = getCurrentIncidentNum And
       B.StaffID Is Null

Wayne

 

[WayneRyan]

poporacer,

Just wanted to avoid using the --> Where StaffID Not In (Select ...)

Wayne

 

[poporacer]

Wayne,...Thanks but it doesn't seem to work....When I ran the form, the query was blank. Right now the tables are blank with the exception of the Staff table. I put in a breakpoint to make sure that the getCurrentIncidentNum had the incident number and it worked. I changed the getCurrentIncidentNum to getCurrentIncidentNum ()
Any suggestions?

 

[WayneRyan]

popo,

1) Make sure that the function is in a Public module

2) You can replace the function call with Forms![MainForm]![Subform].Form!IncidentID

Other than that, I'd have to see it.

Wayne