Duplicate data on combo box


K

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
 
Ad

Advertisements

K

Keith Wilby

Karen said:
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
 
J

JEA

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

Keith Wilby

JEA said:
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.
 
J

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?
 
J

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?
 
Ad

Advertisements

L

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
 
Ad

Advertisements

K

Keith Wilby

JEA via AccessMonster.com said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter based on value in combo box 1
dependent combo boxes 1
Auto Fill 0
Combo box problem 7
Combo box help! 3
Combo Box 6
Form help 4
Combo Box, Unbound Text Boxes 2

Top