Solved Sort an Access Table...

[Gizem]

Hello again and a Happy New Year,

today i have an other problem. I have an Access table and now i would like to sort it numerical.
MyTable seems like:

ID	POSITION	NAME
78	0	Mueller
79	2	Baecker
80	1	Scholz

It is important by sorting it, to take the whole row like:

ID	POSITION	NAME
78	0	Mueller
80	1	Scholz
79	2	Baecker

The order of Colomn two and three is important. I have to export the data of the NAMES by the right order.
I have read already that i need a query by coding.

 

[June7]

Yes, use Query Builder to construct. Sort on POSITION field. Exactly what is the difficulty you are having with this basic task?

 

[oleronesoftwares]

SELECT table.id, table.position, table.names
FROM

ORDER BY table.position;

Is there a field called name, that is a reserve keyword in ms access.

 

[Pat Hartman]

If Position is not unique, you will need a second or even more columns in the sort.

If you don't want to change the order of the columns in the Select clause, then add the columns you want to sort by in the hierarchical order you want them to be sorted at the end of the selected columns. Make sure to uncheck the show box to ensure that the columns are not added twice to the output.

 

[The_Doc_Man]

Just a bit of nomenclature here...

You cannot sort a table. Tables do not inherently have any order. The sort attempt usually doesn't "take." It is kind of a side-effect of the set theory that is the underlying basis for database operations.

You CAN, however, create a query to present all of the records of that table but sorted in a particular order. Forms and reports don't usually care that you gave them a query rather than a table as a record source.

 

[June7]

Wait, a table can be saved with a sort order applied. However, only one field sort can be applied. When first selecting table as form RecordSource, it will pick up the table sort criteria and set the form Order By property. Same goes for a filter saved with table although the filter will be toggled off when table is closed and have to toggle on to apply filter when reopening table.

So, filtering/sorting on table is not a practical means to dynamically pass these criteria to form or report.

 

[Pat Hartman]

Access fools us by remembering the last sort we applied when we open a table in DS view. It looks like a query and acts like a query because behind the scenes, Access has to make a query to retrieve the data. Reading a table is technically nothing at all like reading a flat file.

When a C&R is run, Access reorders all the rows of all the tables into PK sequence so even if we didn't actually sort the "table", it still appears to us to be sorted. Most people never notice that rows can actually be displaced by the update process.

 

[Gizem]

Hello together,

i had to add a query and sort it in it. I used Order By.

Thank you very much

 

[Pat Hartman]

According to relational theory, tables and queries are unordered sets. The only time you can determine row order is by specifically sorting using an Order By clause. Otherwise, rows are like marbles in a bag. They're all there but you can't predict how they will come out. As we discussed earlier, it isn't actually that random and Access makes it look even less random than it is because of how it sorts by PK during a compact and how it retains memory or prior order by actions in DS view. But if you need a specific order, you have to sort to ensure it. You would see more randomness in SQL Server because it doesn't "help" you the way Access does.