Limiting a Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Br. Vincent - 12 Nov 2007 02:16 GMT

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.

Reply to this Message

Allen Browne - 12 Nov 2007 02:55 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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.

Reply to this Message

Br. Vincent - 14 Nov 2007 21:55 GMT

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.

> Try something like this in the RowSource of your combo:
> SELECT DISTINCT State
[quoted text clipped - 13 lines]
> >the
> > list to specifically hose States that I acually have customers.

Reply to this Message

Allen Browne - 15 Nov 2007 00:15 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thank you, I will explain more.
> Very simple I have to two fields. 1 StateID | 2 State_Code.
[quoted text clipped - 22 lines]
>> >the
>> > list to specifically hose States that I acually have customers.

Reply to this Message

Smartin - 15 Nov 2007 00:23 GMT

On Nov 14, 4:55 pm, Br. Vincent <BrVinc...@discussions.microsoft.com>
wrote:

> Thank you, I will explain more.
> Very simple I have to two fields. 1 StateID | 2 State_Code.
[quoted text clipped - 23 lines]
> > >the
> > > list to specifically hose States that I acually have customers.

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;

Reply to this Message