Help fixing a combo box (return duplicate values only once)

Question

I have created a lookup list for one table based on data from another table. The list contains duplicate values. How can I fix this? Here is a copy of the SQL

SELECT [Rabbit Information].ID, [Rabbit Information].Gender FROM [Rabbit Information];

Any ideas would be very appreciated.

Susan

Answer 1

Your previous query had 2 columns, this one has only one. You need to design your form and change a few properties of the listbox, specifically the ColumnWidths and ColumnCount properties.

Answer 2

As was pointed out above: most likely because you have other fields in your query. Can you remove the ID column?

Answer 3

Don,

I originally had this field set to a list box with values I typed (fixed list), but I had to change it to a lookup based on the other table to create a relationship between the two tables so that when I entered a new rabbit into the rabbit info table this table would update also.

Note: Doing this added an expansion icon at the left-side of all my records in Rabbit Information table and I have to also select the gender there to get the Rabbit Name (Master) table to update with new entries. The Rabbit Name (Master) table supplies two different fields (Sire and Dam) with a list of names based on gender to select from for these fields. This is necessary for me to be able to prove the pedigree of my rabbits.

Tried your code and now the box returns no values.? Any thoughts on how to fix this? Also any way to stop having to choose the gender in the expansion (what I mean is can I just choose the gender in the Rabbit Info table and have the Rabbit Name (Master) table update with both fields of information)?

Susan

Answer 4

Two things:

1.) Do not include the ID field if possible, because this is what is making each record unique.

2.) Now you can make the query (SQL) supply only unique records with the addition of DISTINCTROW.

SELECT DISTINCTROW [Rabbit Information].Gender FROM [Rabbit Information];

In your particular example, though ... where there are only two choices of gender(at least the last I've heard! <grin>) MALE or FEMALE ... it may be just easier to use a fixed list rather than running a query.

Answer 5

Use the DISTINCT keyword, or while in query design view, click on the design surface background, right-click > Properties, and set Unique Values to True.