Report not sorting as it should 1

[msc0tt]

I used the Report wizard to generate my report. I requested it to sort by field 'sortname' yet it prints unsorted. As a test, I entered the report's SQL into the query tool, and the results are sorted correctly!
Am I missing something? -with thanks

SELECT [guests].[sortname], [items].[itemID], [items].[desc], [items].[bid], [guests].[guestID] FROM guests INNER JOIN items ON [guests].[guestID]=[items].[guestID] ORDER BY [guests].[sortname];

 

[scriverb]

The Sorting and Grouping property of your report should be checked to see if you indicated some other fields to sort your query on. You see the query is not the last line of sorting for a report. The report has a section of its own that it uses to sort and group records. During the Wizard process you probably responded to its prompts and indicated some other fields to sort on.

The remedy is to click on the Sort and Grouping button in the design window of the report. In the left hand column their will be a list of fields that the report is sorting on. This is where you need to make modifications. But, be careful because if you have group headers and footers then they must stay to have the report function as you have indicated.

Let me know if you have any trouble with this. Bob Scriver

 

[msc0tt]

Bob,
Thanks for the offer of help. There is one entry in the Sorting/Grouping dialog: guestID. I tried adding a second entry of sortname, but this had no effect.
Here is my situation:
Two tables: guests and items. They are related one-to-many on the field: guestID. Each guest record may have: none, one, or many items tied to it.
I wish my report to list all guests with at least one item record linked. The output will be:

Deer, Jane (this is the contents of the 'sortname' field)
Item #5
Doe, John
Item #1
Item #4
Item #17
Smith
Item #3
Item #9

Any insight is appreciated!
Mike Scott

 

[scriverb]

First of all change the GuestID selection in the Sorting and Grouping to SortName. This should clear up the sorting problem. Now run the report and let's assess what it looks like.

Get back to me if there are other problems. Bob Scriver

 

[msc0tt]

Gads, how embarrasing! All is well in my world now. Thanks again.

 

[scriverb]

Hey don't be embarrassed. We all have had days when you can't see the forest for the trees. I have had plenty of them. Bob Scriver