I am trying to get a listing of states where we have people that need to be called. Once all the people in the state are called, I want the state to be removed from the listing of states. I currently have this statement:
SELECT DISTINCT StateAbbrev, StateName FROM RepStates WHERE (StateAbbrev IN (SELECT State FROM Contacts WHERE (Yeswecalled IS NULL))) ORDER BY [StateName]
This will give me all of the states that are in the database, even if there is noone in the state to call. My thinking was to check CONTACTS to see who still needs to be called, and populate the dropdown list with the list of states that is created, using DISTINCT to make sure that any state is represented only once. Can anyone help me fine tune this?
RepStates has 2 fields only, StateAbbrev and StateName.
Contacts has the names and addresses, along with wether they have been called or not (the field [Yeswecalled])