Order by rownum or order that data was entered into table

[Cindy H]

Hi

Is there a way to order a table in the same order as the data was entered?
I know in Oracle you could do this using rownum, but I have not been able to
find anything in Access for this.

Thanks,
Cindy

 

[BabyATX13 via AccessMonster.com]

Make your key field an autonumber and sort ascending on that field.
K Board

 

[Will Lastname]

Or if you have a date/time field you can sort that way. BabyATX13's
idea is probably best but it's always nice to have options.

 

[peregenem]

Or if you have a date/time field you can sort that way. BabyATX13's
idea is probably best but it's always nice to have options.

The DATETIME approach is best when you want to maintain a 'date edited'
value e.g.

CREATE TABLE Test1 (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL,
effective_date DATETIME
DEFAULT NOW() NOT NULL,
CHECK(effective_date = NOW())
);

INSERT INTO Test1 (key_col, data_col) VALUES (1,1);
INSERT INTO Test1 (key_col, data_col) VALUES (2,2);
INSERT INTO Test1 (key_col, data_col) VALUES (3,3);
UPDATE Test1
SET data_col = 99, effective_date = NOW()
WHERE key_col = 1;

In other words, you can easily ensure the effective_date reflects the
date the was created or changed but you have to take further action to
ensure it always only reflects the date the was created (e.g. revoke
permissions and create WITH OWNERACCESS procedures to INSERT and UPDATE
respectively). Also, for Jet DATETIME is only accessible at 1 second
accuracy, a problem if you could have more than one row creation per
second.

The autonumber (IDENTITY) approach may be better because it cannot be
subsequently updated:

CREATE TABLE Test2 (
key_col INTEGER NOT NULL PRIMARY KEY,
data_col INTEGER NOT NULL,
row_ID INTEGER IDENTITY(1,1) NOT NULL
);
INSERT INTO Test2 (key_col, data_col) VALUES (1,1);
INSERT INTO Test2 (key_col, data_col) VALUES (2,2);
INSERT INTO Test2 (key_col, data_col) VALUES (3,3);
UPDATE Test2
SET data_col = 99,
row_ID = 999
WHERE key_col = 1;
-- ERROR: cannot update 'row_ID'

The autonumber can be explicitly set on insert but the next
auto-generated value will be re-seeded e.g.

INSERT INTO Test2 (key_col, data_col, row_ID)
VALUES (4, 4, 999);
INSERT INTO Test2 (key_col, data_col) VALUES (5, 5);
SELECT row_ID
FROM Test2
WHERE key_col = 5;
-- returns 1000

I suppose there is a potential issue here:

INSERT INTO Test2 (key_col, data_col, row_ID)
VALUES (6, 6, 2147483647);
INSERT INTO Test2 (key_col, data_col) VALUES (7, 7);
SELECT row_ID
FROM Test2
WHERE key_col = 7
-- returns 1001

If you *really* need to ensure row_ID order cannot be violate in this
way, you are back to revoking permissions and/or 'manually' allocating
row_IDs.