Remove Duplicates From Combo Box

[Argonaught]

I have a combo box linked to a field [Author] that displays all books by a specific author.
This works fine but as the [Author] field contains multiple instances of authors names, the combo box will display the same name multiple times.
Can i set it so it only shows the authors name once even though there are multiple entries under that name?

For example: there may be 10 book entries for J.K Rowling so the combo box will list J.K Rowling 10 times, i only want it to be displayed once.
I have included a example db1.zip.

Cheers
Argonaught

P.s i have tried to link the combo box to exsisting queries but i get an error message when using the wizzard that says "No value given for one or more required parameters.

PPs do you know how i can make my contious forms list A-Z by author as a default?

 

[Kodo]

Take a look at how I did this..

 

[Argonaught]

This is o.k but it lists "rowling, rowling" instead of one entry of "Rowling, J.K"
which is what im after.
Cheers
Argonaught

 

[JC10001]

This is o.k but it lists "rowling, rowling" instead of one entry of "Rowling, J.K"
which is what im after.
Cheers
Argonaught

Open up Query2, change to SQL view and paste this code to fix it.

SELECT Authors.ID, [LastName] & ", " & [FirstName] AS Name
FROM Authors;

 

[Kodo]

Open up Query2, change to SQL view and paste this code to fix it.

SELECT Authors.ID, [LastName] & ", " & [FirstName] AS Name
FROM Authors;

oops.. sorry.

 

[Argonaught]

Im not sure i want to know the answer to this.
Have i laid my original table out incorrectly by including all my fields in one table (as in the original zip).
The db you modified now has an author table and all authors in the original are linked to id numbers.
This gives me the correct responce in my combo box, but will i have to repace each author name with a number and manually re-enter them in an author table? Also how does this work when adding new records?

Cheers
Argonaught

 

[ColinEssex]

Ideally, you should have a table (like a masterfile) of authors. It should contain 2 fields, an ID Autonumber and the Author name. That way you post the ID number against the book (via the ComboBox) and not the name. This cuts down on the inevitable spelling errors (Rowling JK or Rowling J.K.) also should you have to amend a name, you only amend it once in the authors table and not loads of times in the book table. The ID number in the book table will pick up whatever name is against it in the authors table.

Plus you will only get one instance of each name being shown in the ComboBox and you can LimitToList so that others can't be added unless they are first entered in the authors table.

Col

 

[Mile-O]

Go with what Col says - properly normalise your tables to reflect one for authors, one for books, one for genre, one for publishers, etc.

However, the SQL for your combo can also be, for example:

SELECT DISTINCT Author FROM tblBooks ORDER BY Author;

One last thing - book yourself some time with the local flegellator for owning anything by JK Rowling in the first case.*


* Unless you are 14 or under, then it's understandable.

 

[Mile-O]

Personally, though, I'd use three tables:

tblAuthors
AuthorID
Forename
Initials
Surname

tblBooks
BookID
Title
ISBN
YearOfPublication
PublisherID
GenreID
other dependant book fields

tblAuthorsToBooks
AuthorID
BookID


This structure would allow for books with mutliple authors.

 

[Argonaught]

I remade my tables and linked them via a query as was done in the above example.
This all works, but i cannot now edit the records as i could before and i cannot add a record.
I am not very experienced with access and after lots of attempts i have found myself back here!
Can you point me in the right direction?
I have included an example DB.zip so you can see whats happening.

Cheers
Argonaught

 

[Kodo]

it's because you're querying more than one table.

You need to make the main form list only the authors and then make a subform that lists the relevant information for that author.