Incorrect alpha sort on report & query

[Dano]

I have a simple db..2 tables & 1 report. Table 1 is "Author", has 2 fields, ID & Author, which sorts Author correctly. Table 2 is "Books", has 3 fields, ID, Author and Title. The Author field is a lookup from Author in Table 1. The report uses grouping & sorting but the Author sort is incorrect. I have also tried a query to sort the author & title from Table 2 but it also sorts incorrectly. I get the same results in Access 2007 & Access 2010 both which are running on different Win 10 machines. Is this a known bug? I have attached the db Books ET.accdb

[orange]

What exactly isn't working the way you want? Show us the issue.

Were the Titles suppose to be sorted alphabetically within Author?

It is considered by many that you should avoid Lookups at the table field level.

[Dano]

I was not aware of the caviat with using a lookup at the table field level. I see you ran the report. Farther down the report the sort on "Author" falls apart..notice the random listing which should be an alpha sort based on the Author. The Titles are not sorted. I get the same random sort using a query.

I have also included the report design view showing the grouping & sorting config. If I export the table to Excel the Author sort works correctly.

Is this a config issue or Access bug? How can I get a good sort?

[orange]

Your issue is the use of the lookup. With the lookup in place your query doesn't sort correctly either.

[Dano]

Take a look at the report sort.pdf I submitted, specifically the red circled areas. A proper sort would have the authors listed in alphabetical order as opposed to what it produces. Am I expecting something that is incorrect?

[orange]

Do you have the raw data for your tables?

I'm not sure exactly what is wrong, but
-I don't see your autonumbers when I look at your data in datasheet view??
-your query doesn't sort correctly
-you have 3 records in author table with no (NULL) author

My best guess is it stems from your use of Lookup at table field level.

You have authors and books
An Author can write 1 or more books
A book may have 1 or more authors

In my view the proper relationships would be as follows:

Author--->AuthorOfBook<--- Book

AuthorID -----> AuthorID,BookID<----BookID
AuthorName                        BookTitle

[ssanfu]

@orange,
If you open both tables in datasheet view, the "ID" (poor name) field is hidden.

Along with orange's comments:
I would split the "AuthorName" into two fields: "AuthorFName" and "AuthorLName". Much easier to combine (concatenate) First and Last names than to split them.
Sorting appears to be by first name instead of last name. Unless you look for "Janet", instead of "Evanovich"?

Sort order in a query does not affect a report sort order. The report has its own sort capability.


Suggestion: get rid of the look up FIELD.
Use a main form/sub form to enter/view data.
Can have two form: one with the main form to select/enter the author and the sub form to enter many books.
One with the main form to select/enter the book and the sub form to enter many authors.

[orange]

Thanks Steve.
Good points. Jeez, I don't know why M$oft went with the lookup field.

I was going to offer to redesign and build the tables with the OPs raw data.
But you have described the process well.
The OP should review some concepts and design materials

Intro to Database
The Relational Model
Data Modelling and the ER Model

[Dano]

Solved it! I removed the "Lookup" in the table field & everything works as it should. Thanks for the help & education.

[orange]

ssanfu and I were happy to help.
Good luck with your project.