Duplicate data on combo box

[Karen]

I have a combo box on a form that is fed from a table where customer info is
entered.. name address city state zip... The combo box is for the city
field. However, if there is more than one customer from NEW YORK it is
showing new york twice instead of once. How can I change that?
Thanks

 

[Keith Wilby]

I have a combo box on a form that is fed from a table where customer info
is
entered.. name address city state zip... The combo box is for the city
field. However, if there is more than one customer from NEW YORK it is
showing new york twice instead of once. How can I change that?
Thanks

Use Select Distinct in your combo's query.

Select Distinct [Fieldname] from [tblTablename]

Keith.
www.keithwilby.com

 

[JEA]

You're table structure could be an issue. Could you post a screen shot of the
table relations?

 

[Keith Wilby]

You're table structure could be an issue.

How so? If the OP has a "City" field with duplicate entries then a select
distinct query will cure the problem.

 

[JEA via AccessMonster.com]

She may be getting the list of states from the wrong place. It may require a
seperate 'Lookup' table containing all the possible state she wants.

If she's getting it from the table where customer info is stored, what
happens if she wants to add a new state? Is she going to have to enter a
whole new customer record containing just the new state?

 

[JEA via AccessMonster.com]

Sorry, for 'state' read 'city'

She may be getting the list of states from the wrong place. It may require a
seperate 'Lookup' table containing all the possible state she wants.

If she's getting it from the table where customer info is stored, what
happens if she wants to add a new state? Is she going to have to enter a
whole new customer record containing just the new state?

 

[Linq Adams via AccessMonster.com]

I tend to agree with JEA. The key column in a combobox, in most cases,
should be a unique field. As suggested, the usual way of doing this would be
to have a separate table of cities and base the combobox on that. Actually,
the really appropriate thing to do would be to have a table with City, State
and Zip. That way, only the Zip Code need to be redundant (in every customer
record) rather than having City/State/Zip in every record. This is really
what normalization is all about!

Another approach wold be to have two cascading comboboxes, wher the City is
selcted from the first cbo, then the recordsource of the second cbo iselects
only customers in that city.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Keith Wilby]

She may be getting the list of states from the wrong place. It may require
a
seperate 'Lookup' table containing all the possible state she wants.

If she's getting it from the table where customer info is stored, what
happens if she wants to add a new state? Is she going to have to enter a
whole new customer record containing just the new state?

All valid points. I really should have engaged my brain before asking that
one ;-)

Keith.