Limiting a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am pulling data from a listing of all 50 States. I would like to limit the
list to specifically hose States that I acually have customers.
 
Try something like this in the RowSource of your combo:
SELECT DISTINCT State
FROM Customers
WHERE State Is Not Null
ORDER BY State;

This assumes you have a table named Customers, with a field named State.

A weakness of this approach is that it may not be obvious how to add a new
customer from a state where you've never had a customer before. If state is
a text field, and the user can guess the correct text, and there is no
hidden column here, it may be possible by leaving the combo's Limit To List
property set to No.
 
Thank you, I will explain more.
Very simple I have to two fields. 1 StateID | 2 State_Code.
State Code is all 50 states. I only have customers in 10 of the states. I do
not want to scroll all 50. I have filtered DC out from the the list.
 
Why not just delete all the irrelevant states from your State table?

Less efficient alternative would be to make the RowSource a query that
limits the states to those that match the customers:
SELECT DISTINCT State.StateID, State.State_Code
FROM State INNER JOIN Customers
ON State.StateID = Customers.StateID
ORDER BY State.State_Code;

There will be no benifit to the less efficient approach, as you still won't
be able to select a new state.
 
Thank you, I will explain more.
Very simple I have to two fields. 1 StateID | 2 State_Code.
State Code is all 50 states. I only have customers in 10 of the states. I do
not want to scroll all 50. I have filtered DC out from the the list.
Click to expand...

If your table only has StateID and State_Code how do you know which
states have customers? I am guessing you have a customers table that
joins to the StateID (as opposed to State_Code) or Allen's solution
would work. If this is the case try this SQL in the combo's RowSource:
SELECT DISTINCT States.State_Code
FROM Customers INNER JOIN States ON Customers.StateID = States.StateID
ORDER BY States.State_Code;
 
Back
Top