Group field in report by ID order (1 Viewer)

[eristonmattos]

Good afternoon everyone,
The DB has a form that opens the report according to the date. I put all the objects that make the example work.
The report with data from a query, where I need it to show the record order, but when I group by neighborhood, it returns in alphabetical order, how to solve this?

The DB is this: dropbox.com/s/0g6v2xdtvmqrtod/Relat%C3%B3rio%20modelo2.accdb?dl=0

I hope to be able to count on the members.

 

[Gasman]

Crossposted first at https://www.accessforums.net/showthread.php?t=86389 and solution given

 

[Pat Hartman]

Hi. You've been a member for more than two years but this is your first post. Welcome

Please upload the database here so it can be checked for viruses. No one will download the db from your dropbox. To guard against trolls, the website security requires you to have 10 posts before you can upload anything so you won't be able to do it immediately. I'm not sure if you can just make ten posts and then upload something. There might be a time element also.

If neighborhood is text, then that is the expected order.

Relational tables do not have a sequence number. An autonumber, if that is the PK of the table, is usually used to determine the order in which a row was inserted because most people use the default of Increment rather than the option of Random. So, you would not group by the autonumber, but you would sort by it.

PS, what was wrong with the other solution?

PPS, if you are going to cross post, please include a link to the other thread so we don't waste our time trying to solve a problem that has already been solved. This will garner no friends on any forum where volunteers donate their own free time to help you to solve YOUR problems.

 

[arnelgp]

is this what you meant.
https://www.dropbox.com/s/guqogg9poma5lgx/Relatório modelo2.zip?dl=0

 

[eristonmattos]

The report has the structure the way I need it, but it's not showing the neighborhood field in the order of registration, take a look

 

[The_Doc_Man]

First question, related to this in your post: The report with data from a query, where I need it to show the record order, but when I group by neighborhood, it returns in alphabetical order, how to solve this?

Are you attempting to perform the GROUP BY in the query?

For reasons not totally understood, Access reports have their own way of ordering and grouping. Doing it in the query won't work. You have to use the report builder's method to insert ORDER BY and GROUP BY. It is almost exactly like the Report Wizards read the SQL of your query and remove the ORDER BY and GROUP BY clauses entirely. No, don't ask me why it does that. I have no clue, but it is what it is.

The next question is whether you meant that there is some numeric element to the neighborhood but it is being returned alphabetically by the name of the neighborhood. OR did you mean that it was alphabetically with respect to another field?

 

[Pat Hartman]

Reports rewrite your RecordSource query because Access "knows better". Therefore, you need to control sorting and grouping using the Report interface rather than with your query.

Sorting by a text value rather than the ID means that the text value must be in the query. Therefore, modify the query to do a left join to the lookup table so you can pick up the text value from that table instead of the numeric value from the main table. In the sorting and grouping, sort on the text value.