How do i prevent duplicate choices from appearing in my combo box

[Guest]

I am very new at this, so sorry if this is a stupid question. I have an
Access 2003 Database of all my CDs. It has 1 table which has fields for
Artist, Album, Year, Genre, and up to 31 for the individual tracks.(because
one of my cds has that many)

Anyway, in my Form I have a combo box for Genre. I have got it to pick the
selections from my Genre column on my Table. The problem is there is about
200 or more selections in the drop down box for "Rock" because it has added 1
for each occurance of it in my table. I want to know if there is a way
(possibly in my Query) to filter out the duplicates? I wanted it this way so
that if there was another Genre not listed, when you added it to a new
record, it would automatically be added to the drop down list in my combo box.

Any simple way to do this?
Thanks

 

[tina]

STOP. suggest you read up on data normalization, and design your
tables/relationships correctly before you go any further in building this
database. if you continue with the non-normalized design you have now,
you'll have nothing but problems to fix - might as well do it right the
first time, and make things a lot easier on yourself in the long run. for
more information, see http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[John Spencer]

Another thing you can do is to make sure that your combobox is based on a query
and the query is set to return only unique values.

The SQL of the source for the combobox should look something like

SELECT DISTINCT [Genre]
FROM [The Table]
ORDER BY [Genre]

You are probably much better off creating a separate table as advised elsewhere,
but the above technique will work. It is just slower than having the data in a
lookup table.

 

[Guest]

I agree that you need to normalize.
You say you have fields for 31 tracks. Some CDs have a different artist for
each track - how do you treat these in your database?

Presently you have a spreadsheet with each CD in a row.

You need tables in a one-to-many (1-m) relationship. CD_ID with title
(Oldies from the 80's) related to CD_Tracks.
CD_Tracks has CD_ID, Artist, Album, Band (Group), Year, Genre, etc.
You also might want to build 1-m tables for Artist (Singer), Band, and Genre.

If you search there are already music databases designed, free for download.