By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Table with no PK- need to know order data was entered

P: n/a
MJC
Hi all,

I have inheireted a database with no primary key and need to determine
the order in which the records were entered. The table in question
contains assessments and they are linked to an Intake table. The
foreign key in the Assessment table is the Primary key in the Intake
table (IntakeID). It's a one to many relationship and RI is not
enforced so there is no "hidden" or "system" Index.

The Assessment table is sorting itself based on the Intake ID. When I
copied the Assessment table to a new table and added an autonumber PK
the order of the records changed. Would adding a PK after the fact
revert the data back to the order entered?

Does Access keep an index of the order entered? Is this index wiped
out if the database is repaired and compacted?

TIA

Mike
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Access doesn't keep any information about the order in which the records
were entered. Adding an autonumber after the fact will most likely number
them in the physical order in which they are in the table, which isn't
necessarily the order in which they were entered. Relational database theory
really doesn't support the concept of entry order. Tables are "sacks of
data": the records are put in wherever the DBMS can fit them.

While it's obviously too late now, what you needed was to include a
DateEntered field, with a default value of Now.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"MJC" <ja******@frontiernet.net> wrote in message
news:58**************************@posting.google.c om...
Hi all,

I have inheireted a database with no primary key and need to determine
the order in which the records were entered. The table in question
contains assessments and they are linked to an Intake table. The
foreign key in the Assessment table is the Primary key in the Intake
table (IntakeID). It's a one to many relationship and RI is not
enforced so there is no "hidden" or "system" Index.

The Assessment table is sorting itself based on the Intake ID. When I
copied the Assessment table to a new table and added an autonumber PK
the order of the records changed. Would adding a PK after the fact
revert the data back to the order entered?

Does Access keep an index of the order entered? Is this index wiped
out if the database is repaired and compacted?

TIA

Mike

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.