Problem with Order on Report

[RobertJohn]

Hi all.

I am a beginner using Access 2007 to try to develop a small home library application to store details of my book collection.

I originally had trouble with my data entry forms but those problems have now been resolved thanks to help from this forum.

My difficulty now is that my report won't print the records in the order I want.

My database has three tables as follows:

Table 1 Books has fields Book_ID (AutoNumber, Primary Key) and Title.
Table 2 Authors has fields Author_ID (AutoNumber, Primary Key), First_Name and Last_Name.
(These will be extended to include details like Publisher, fiction or non-fiction etc when I get my basic structure right.)
Table 3 Books_Authors with fields Book_ID and Author_ID (both Number fields and both Primary Keys).

This third table (a junction table) is there to enable a many-to-many relationship between Books and Authors, as one author can have several books and one book may have several authors. I have created a one-to-many relationship between Books and Books_Authors on the Book_ID field, and a similar one-to-many relationship between Authors and Books_Authors on the Author_ID field.

I also have a query (qryAuthors) based on the fields of the Authors table and with a "calculated" field, Full Name (Full Name: [First_Name] & " " & [Last_Name]) which is sorted on the field Last_Name. (When I open the query I get the authors listed by their full name but in order of their last names.)

I am trying to create a report which will list all of my authors (using their full name but in order of their last name) together with their books. I used the Forms Wizard to create a form based on qryAuthors and also the Books table to get the book titles, but the records are listed in order of the Author_ID field, not the Last_Name field. The Wizard gives me the option to order the books under each author, but not the authors themselves.

Can anyone suggest what I am doing wrong, so that I can get my records into the correct order?

Thanks in advance

Robert

 

[dayna]

Try this: On the query that you're using to calculate and sort the authors' names, add the Books_Authors and Book_ID tables so that they're showing alongside your Author_ID table. (Lines should appear between corresponding field names in your tables such that if Books_Authors is in the middle, they look like a little chain gang.)

Next, double-click the Book_ID field of your Books_Authors table and the Title field of your Book_ID table so that those two fields appear in the grid below. In the Book_ID field of your grid, you may want to uncheck the "Show" box. You could then sort the Title field in ascending order, if you like.

Look at the query in datasheet view to ensure that it's returning the desired result, then base your report on that query. Good luck!

 

[Rabbie]

Reports don't use the sort order in the undelying query. They have their own sort and Group parameters. You will find this by clicking on the black dot at the top left hand corner of the report when it is open in Design View

 

[RobertJohn]

Thanks Dayna and Rabbie

The Sorting and Grouping option worked well. Thanks again.

Robert

 

[Rabbie]

Glad to hear you got it sorted. Happy to help