Access97 Query: Excluding List of Names

[BYarn]

I have a query to run which produces a list of staff names and a count of their customers. Sometimes our computer system will in-advertantly assign their managers name to the customer.
I want an expression/criteria which will exclude the manager names from the results, WITHOUT listing each individual name (so if the names change my formula is the same) in the criteria-Could I, for example, have the manager names in a table and exclude all people in the table using the criteria?????????

 

[mike777]

Sure thing. Just tested it out. You can use a query with a subquery.

If your table is called StaffSalesData and your new table is ManagerNames,

select StaffName,CustName
from StaffSalesData
where StaffName not in(select ManName from ManagerNames);

It might be obvious, but the second "select.." is the subquery.

Hope this helps.

-Mike

 

[BYarn]

Thanks Mike!! It didn't work, so please bear with me, this is not clicking for me or I'm not clear.

Let me restate: I have a table called Staff which lists ALL of our staff (lets say salespeople AND managers). It is linked to another table called customers. In addition to other info, the results when linked in the query will produce lists,or counts or...etc of the customers along with their salesperson. Sometimes the customers get mis-assigned to managers and not their salesperson.

I'm need to do some counting but when the list is produced (that I use to do the counts) I want staff people who are managers EXCLUDED from the list. SO if I produce a list or do counts of how many customers each staff person has, the managers will be disregarded and only the actual salespeople counted.

ok-so there is a staff table, a customer table, and I can create a manager table. My query currently used the staff table linked to the customer table.

Hope this doesn't give anyone a headache ( :

Thanks

 

[mike777]

Do you want those customers that have been mis-assigned to be accounted for somehow?

I mean, would seem to me you would want to know which customers have been mis-assigned, so you could properly assign them. This would make your per-salesperson sales numbers accurate.

Sorry, that is really a separate issue; I hope it is relevant.

Based on your follow-up, I don't see where we missed the mark before. It seems to me like I understood the first time correctly, and that the solution should still work.

What do you mean, "...it didn't work...". Did you try it and it didn't work, or do you not think it solves your problem? If it didn't work when you tried it, maybe there is a problem with syntax or something?? I don't know...don't know how I can help...please advise further.

Thanks.
-Mike

 

[BYarn]

The mis-assigned don't represent a large # so for #'s sake we aren't worried, I do plan on producing a separate "exception" report to ID who is missassigned and fix it

I think theres' a syntax issue in part: for one, this it gives me a systax error unless I put in (more than your) brackets, etc. When I do put them in I get a "one one record can be returned by this subquery" error. Also I'm not sure what (to substitute) the CustName part of the "select StaffName,CustName" is (which of my fields or??

Sorry..........