Sort Order Problem with Query Question and Max Value Query Question

[TINMAN]

Attached is a Microsoft Access file - BooksToConsiderReading.

I have a question on the query - BookProgressAllBooks.



I have the field - BK-Nbr-Progress sorting in ascending order. The field at the right end of the fields in this query – CALPERCENTAGE is sorted in ascending order.

This query does not seem to be sorting correctly, what am I doing wrong?

Also, I would like to set up a query that would show the maximum progress for each book. For the database records currently in the database, it would give these results.

BK-0001 63.86%
BK-0002 50.72%4
BK-0003 100.00%
BK-0004 10.95%

How can I set up a query to give ONLY the maximum reading results for all books?

[davegri]

As for the sort, sort on BookTitles.Book_Nbr rather than BookProgress.Bk_Nbr_Progress.
Bk_Nbr_Progress is a table lookup field and is showing the Book number but is actually sorting on the book Key field. That's one of the reasons to avoid table lookups.

[davegri]

Lots of typing for all the long element names, but that's good documentation. Everything's a tradeoff..
I got this to work for the new BooksEachMaxReadQuery:
This query is based on the revised query as suggested in post above.

SELECT BookProgressQueryAllBooks.Book_Nbr, Max(BookProgressQueryAllBooks.CALCPERCENTAGE) AS MaxOfCALCPERCENTAGE
FROM BookProgressQueryAllBooks
GROUP BY BookProgressQueryAllBooks.Book_Nbr;

[TINMAN]

I did revise the query per your suggestion, and it works just great.

Thanks for your help!

[TINMAN]

Thanks for your reply. It looks like it works just fine. I am a very basic Access user and I am not very familiar with Visual Basic.

I am going to try and do the same thing using a report, just hiding the detail line. I should be able to get the same result, I hope.

I will your suggestion to help improve my knowledge of Visual Basic.

Thanks very much for your help.

[davegri]

Glad to help.
Access has 2 Native "languages ". The language we're working with here is not VBA (Visual Basic for Applications), it's SQL (Structured Query Language). SQL deals with interactions with database objects, sometimes called CRUD (Create, Read, Update, Delete). The world is full of acronyms.

If you want a report to show more or less exactly like the query, just use the wizard to create the report based on the query. Each book will be a row in the detail section.

[TINMAN]

Thanks for letting me know that the language you used was SQL. I will have to read a basic book on that topic. Although, it might take a little getting used to for this Octogenarian. I miss the days of working with Fortran over 60 years ago.

Thanks Again!!