Forum Home
Press F1
 
Thread ID: 23909 2002-08-29 03:15:00 Access2000 - order of records in a table tbacon_nz (865) Press F1
Post ID Timestamp Content User
74737 2002-08-29 03:15:00 I have a table with a primary key that is a number (the year). If I retrieve records from this table via openrecordset, i.e.

set rst=db.openrecordset("table1")

I would have expected to retrieve them in key order, i.e. oldest year first. What appears to have happened is that I retrieved them in data entry order. Do I have a basic misunderstanding, or is there something else going on?

TIA

Tony Bacon 		tbacon_nz (865)
74738 2002-08-29 03:50:00 Hi, yes I believe this is correct. Use the sort property to re-sort the recordset. Look in Access VB Help under sort property. It gives an example of a recordset as follows .. ..

-- FROM ACCESS 97 VB HELP--
The following example uses the Sort property to set the sort order of a dynaset-type Recordset object based on an Orders table. The records in the rstSorted recordset variable will be ordered alphabetically by ship country.

Note that you first create a dynaset-type Recordset object and set its Sort property, then open a second dynaset-type Recordset object based on the first. Setting the Sort property of the first Recordset object doesn ' t actually affect the order of the records that it contains, so you need to create a second Recordset object in order to see the effects of the sort.

Sub SortByCountry()
Dim dbs As Database
Dim rstOrders As Recordset, rstSorted As Recordset

' Return reference to current database.
Set dbs = CurrentDb
' Create dynaset-type Recordset object.
Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
' Set sort order.
rstOrders.Sort = "ShipCountry"
' Create second dynaset-type Recordset object.
Set rstSorted = rstOrders.OpenRecordset()
' Perform operations with recordset.
.
.

.

rstOrders.Close
rstSorted.Close
Set dbs = Nothing
End Sub 		parry (27)
74739 2002-08-29 05:20:00 Tony,

The method outlined by Parry will certainly work, but is not needed for what I imagine you are doing.

A comment - Your primary key is Year, and a Primary key will not allow duplicates. This gives you one record per year, which may not be what you want. :)

Another comment.
In a relational database such as Access, you probably should not rely on the table for your data ordering. Consider the table just a big bucket of data, to be extracted by a Query (through SQL)

In the case you mention, you could either:

1a. Construct a query on the table, specifying the order you need.

SELECT Table1.*, Table1.theDate
FROM Table1
ORDER BY Table1.theDate;

Note the asterisk will return all fields to your query. (A point - "Date" is a reserved word, and NOT a good name for a Field. Fine for a Caption.)

1b. Save the query (eg. as "Query1")

1c. Open your Recordset on this Query.
set rst=db.openrecordset("Query1")

That will give the result you need.

or

2a Open your Recordset using the same SQL.
set rst=db.openrecordset("SELECT Table1.*, Table1.Chart_Date FROM Table1 ORDER BY Table1.Chart_Date;")

That will give an identical result.

Obviously your name "Table1" was an example, but naming of objects in Access can make your life many times easier. Since (very simplified) you will often want three design steps in Access:

1. Table for Data.
2. Query on Table to filter/sort Data.
3. Form sourced on Query to present Data (for viewing/editing/Report construction)

it's a good idea to follow a Naming convention (there's more than one.)

I'd use:
tblMyDataWithDates for the Table;
qryMyDataWithDates for the Query;
frmMyDataWithDates for the Form.

Then, of course there's:

rptMyDataWithDates bother.
rptMyDataWithDates2 why won't it work?
rptMyDataWithDates3 what's wrong with this?
rptMyDataWithDates4 why won't it work now?
rptMyDataWithDates5 at last. :D

Anyway, if you put the appropriate names into this, open the Immediate Window (control-G), go back to Sub Quick and Dirty Test and hit F5, you should see all your ducks in a row. :)

(Assuming DAO, not ADO. Change it if I'm an ass.)

Sub QADT()
Dim theCount
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Table1.*, Table1.theDate FROM Table1 ORDER BY Table1.theDate ;")
While Not rst.EOF
theCount = theCount + 1
Debug.Print theCount & vbTab & rst!theDate
rst.MoveNext
Wend
Set rst = Nothing
Set db = Nothing

End Sub

For more in-depth help, the Newsgroup comp.databases.ms-access is filled with friendly and intelligent people (much like PressF1, really) who are very happy to help anyone who has READ THE FAQ. (Yes, I meant to shout.) :)

If you need more, try:
www.mvps.org

and for even more:
www.mvps.org

Hope something there helped.

Alan Carpenter 		Alan Carpenter (540)
74740 2002-08-29 07:39:00 Hi, yes Alans suggest of a select query with an 'order by' is far cleaner ... why didnt I think of that? Nevermind. And I can also recommend the mvps site as well - great resource with lots of examples for common and not so common tasks.

cheers
Parry 		parry (27)
74741 2002-08-29 11:50:00 Thanks Parry and Alan. I had already achieved what I wanted with an ORDER ON - I was more interested in the general issue. As I understand it from the tenor of your replies, the primary key has no intrinsic effect on the sequence that records are retrieved from a table, which is what I had assumed. You probably realise by now that I have general programming experience, but am a relative novice when it comes to the intricacies of Access programming. BTW the table in question has basic reference data about an organisation that changes from year to year, so a one-record-per-year is exactly what I wanted.

Thanks again for your quick responses.

Tony Bacon 		tbacon_nz (865)
74742 2002-08-29 22:43:00 Hi Tony, its quite difficult to gauge peoples experience on message boards so apologies if I didnt understand your question fully :-)

Your onto it, primary keys have no effect on sort order with the order determined by when the records were created unless you specifically re-order by some criteria.

cheers
Parry 		parry (27)
74743 2002-08-30 05:14:00 Tks for your help with the original question - which is now resolved thanks to you and Parry. My question now is even more basic - as someone who rarely looks at newsgroups, where do I actually find the FAQ for a particular newsgroup? tbacon_nz (865)
74744 2002-08-30 05:17:00 www.ibiblio.org godfather (25)
1