Query regarding a combo box

[lcook1974]

Hi all,
I have a list box based on a query. I want this list box to NOT SHOW items in the list that have been completed for that particular User. I can get the list box to show what I want (sort of) by using the NOT IN () sub query. However I want the list box to show all the records for the next "new" user that comes up.

I have a table called Completed training that houses the employeeId, ObjectiveID, trainerID (another employee), Date completed.

My combo box is named "cmbEmployee" on a form called "frmCompletedTraining".

Below is the SQL code for the list box.

SELECT ObjectiveTitle.OTID, ObjectiveTitle.OT
FROM ObjectiveTitle
WHERE (((ObjectiveTitle.OTID) Not In (Select ObjectiveID From CompletedTraining)));

Thanks!

Larry

 

[MSAccessRookie]

Hi all,
I have a list box based on a query. I want this list box to NOT SHOW items in the list that have been completed for that particular User. I can get the list box to show what I want (sort of) by using the NOT IN () sub query. However I want the list box to show all the records for the next "new" user that comes up.

I have a table called Completed training that houses the employeeId, ObjectiveID, trainerID (another employee), Date completed.

My combo box is named "cmbEmployee" on a form called "frmCompletedTraining".

Below is the SQL code for the list box.

SELECT ObjectiveTitle.OTID, ObjectiveTitle.OT
FROM ObjectiveTitle
WHERE (((ObjectiveTitle.OTID) Not In (Select ObjectiveID From CompletedTraining)));

Thanks!

Larry

Your Sub-Query needs a WHERE Clause that refers to the employeeId field and excludes the records that you do not want to see.

 

[lcook1974]

Not to be that guy or anything but could give me an example?

should I completely take out the NOT IN() sub-query and do another query?

Larry

 

[MSAccessRookie]

Not to be that guy or anything but could give me an example?

should I completely take out the NOT IN() sub-query and do another query?

Larry

Sorry if my motivation was misunderstood, but I was trying to help you learn as opposed to doing your work for you. Since your Query is the one being evaluated, I will use it as an example.

You will need the Not In statement, since everything after that (Select ObjectiveID From CompletedTraining), is the Sub-Query that I was referring to. Notice that the Sub-Query contains a SELECT statement, and a FROM statement, but has no WHERE statement. Here is where you add a WHERE statement that looks something like:

WHERE CompletedTraining.employeeId = { Value being validated }

 

[lcook1974]

Thank you! I think I am on the right track now....I'll let you know when/if I have trouble.

Larry