Access Tables - Sorting

[jethro11]

Hi,
I'm using VBA code to sort a table after writing out the fields but it doesn't seem to work for some reason. To be specific I want the table (tblLptRtgs) sorted by the fields Order_Number and Oper_Seq in ascending order. Here is my code:

db.OpenRecordset("Select * from tblLptRtgs order by [ORDER_NUMBER], [Oper_Seq] ascending ").

This line of code doesn't give me an error but it doesn't sort it either. Can anyone help me out please??

 

[dhookom]

I don't see a "Set rs ="... Assuming there is an "rs" then are you suggesting that if you add the code:
With rs
Do Until .EOF
debug.Print !Order_Number, !Oper_Seq
.MoveNext
Loop
End With

The above should print the values in ascending order.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[MoLaker]

You must look at the open recordset, not the table to view the sorted results. Also, Ascending order is the default so you do not have to enter it in the sql unless you need a mixture of ascending and descending orders.

db.OpenRecordset("Select * from tblLptRtgs order by [ORDER_NUMBER], [Oper_Seq]").

or

db.OpenRecordset("Select * from tblLptRtgs order by [ORDER_NUMBER] asc, [Oper_Seq] ascending desc").

Lastly, if you are pulling this recordset into a form (using it as a form record source), make sure the form isn't sorting, too.

 

[MoLaker]

Sorry, the last SQL in my previous post should read

db.OpenRecordset("Select * from tblLptRtgs order by [ORDER_NUMBER] asc, [Oper_Seq] desc").

 

[willir]

Jethro11

Can you give examples of Order_Number?

Next,how are you using the RecordSet? Are you expecting the table to be sorted per Order_Number, Order_Seq afterwards?

Access will store records as it may, usually first-in order. Since SQL can be used to sort as required, sorting is only done as required.

Richard

 

[jethro11]

All,
Thanks for taking the time to respond to my post. Each Order_Number has several sequence steps (10,20,30...) and I have several orders in the table (tblLptRtgs). I want it sorted by Order_Number(ascending)first and then Oper_Seq. Here is some sample data for Order_Number and Oper_Seq to illustrate how I want the data sorted in the table.

Order Oper
Number Seq
9495E 10
9495E 20
9495E 30
9495E 40
9495E 50
9495E 60
9497E 10
9497E 20
9497E 30
9497E 40
9498E 10
9498E 20
9498E 30
9498E 40
9498E 50
9498E 60

Also I am using the following code

Set rs2 = db.OpenRecordset("Select * from tblLptRtgs ORDER BY [Order_Number], [Oper_Seq] ascending").

 

[willir]

jethro11

Sorting is dynamic. VBA will run the code, and process the data in the sort order your provided.

...but the actual data in the table will not be sorted.

This is the way relational databases work, and is related to how data is stored in the table. Access / Jet Engine will not "insert" records inbetween other records to accommodate sorting - the record will probably be appended to the end of the table.

However...
- Indexes will control the order and speed data is retrieved. For example by OrderNumber. (But, once again, this may not have the desired results you are looking for,)
- You can use a query instead of a table as the record source for a form or report, and include the ORDER BY clause so data is retrieved in the desired order.

Richard

 

[jethro11]

Willir,
Thanks for your reply,I will try your suggestions. Actually I defined the sort order in the table design view and it works fine. However when I link to this table in another database the sort doesn't work. That's the only problem. I guess I can use a make-table query and specify the sort order in there?

 

[dhookom]

jethro11,
Please understand that records in tables can never be assumed to be sorted. The only order is the sorting you apply in a query. Records in a table are like marbles in a bag or kids on a playground. There is no order until the teacher blows the whistle and has the kids line up to go back into the school. Even then there is no order unless the teacher has the children line up tallest to shortest or oldest to youngest or whatever.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[willir]

Thanks Duane for providing the clarification.

 

[BNPMike]

Please understand that records in tables can never be assumed to be sorted. The only order is the sorting you apply in a query.

Whilst your point is a good one it's as well to point out the actual situation.


Relations by definition are unordered. For those who don't realise this, a key design feature of relational databases is that you can't access data except by specifying data values. Other database models of the time used ordering to carry information. This was deemed bad because it makes an individual data model more arcane and makes it more likely that people will make programming errors. The relational database model was invented for end-users - to make things easier for them.

That said, Access uses tables not relations. Also it does sort tables. Tables are sorted on primary key order. The message however is ignore this. Access works very well relationally so don't compromise things by taking advantage of little quirks.

On this theme I would also add that, again for theoretical reasons, don't use tables directly. Always go through views/queries. One of the many benefits of doing this of course is it ensures you get your desired sort order.