Access 97-multi-field primary key

[Guest]

Field 1 is multiple copies of single numbers, ascending, related to table A;
field two is unique date/time, descending, related to table B. This table C
of about 1600 records sorts properly EXCEPT for 15 records (intermingled)
where field two shows ascending. I have tried: installing sr2b, deleting and
reentering the tables B & C records, and swearing a lot. Any suggestions????

 

[Douglas J. Steele]

It's not clear to me what you're asking about. Is it that the order of the
records in your table isn't what you expect? Never assume anything about the
order of records in tables: tables are unorganized "sacks of data", where
the data's put wherever it fits. If the sequence of records is important,
you must ensure there are appropriate fields to sort on, and use a query
with an ORDER BY clause.

 

[John W. Vinson]

On Sat, 26 May 2007 11:51:09 -0700, Frustrated Phil <Frustrated

Field 1 is multiple copies of single numbers, ascending, related to table A;
field two is unique date/time, descending, related to table B. This table C
of about 1600 records sorts properly EXCEPT for 15 records (intermingled)
where field two shows ascending. I have tried: installing sr2b, deleting and
reentering the tables B & C records, and swearing a lot. Any suggestions????

Well, I'd suggest that you abandon the idea that records in a table *have* any
defined order. They don't. A table is an unordered "heap" of records; it will
be presented in whatever order the JET database engine finds convenient. Often
this will be in primary key order, but there's no guarantee.

If you want to see records in a particular order, you must - no option! - use
a Query sorting the fields in that order. Such a query will be updateable and
should not interfere with your use of the database.

John W. Vinson [MVP]

 

[Tony Toews [MVP]]

Field 1 is multiple copies of single numbers, ascending, related to table A;
field two is unique date/time, descending, related to table B. This table C
of about 1600 records sorts properly EXCEPT for 15 records (intermingled)
where field two shows ascending. I have tried: installing sr2b, deleting and
reentering the tables B & C records, and swearing a lot. Any suggestions????

There was a bug at one point in time with a version of Jet which didn't handle
indexes with descending properly.

I'd double check that you have Jet 3.51 SP3 3.51.3328.0 installed. Although this may
have been a problem in Jet 4.0. Look in the \Windows\System or similar directory for
the above files. Then right click on the file >> Properties >> Version.

There is a similar problem here but I don't think this was it.
BUG: You may notice an incorrect sorted order when you sort the negative decimal
values in descending order in Access
http://support.microsoft.com/kb/837148/en-us

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[DAVID]

Just note that the classic cause of this particular
problem is that the century digits are hidden
(short date format) and incorrect.

(david)

 

[Tony Toews [MVP]]

Just note that the classic cause of this particular
problem is that the century digits are hidden
(short date format) and incorrect.

Good catch. I had forgotten about that as I always use yyyy-mm-dd on my system.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

Thank you. Someone else gave me a clue to explore.

 

[Guest]

Thank you.

On Sat, 26 May 2007 11:51:09 -0700, Frustrated Phil <Frustrated


Well, I'd suggest that you abandon the idea that records in a table *have* any
defined order. They don't. A table is an unordered "heap" of records; it will
be presented in whatever order the JET database engine finds convenient. Often
this will be in primary key order, but there's no guarantee.

If you want to see records in a particular order, you must - no option! - use
a Query sorting the fields in that order. Such a query will be updateable and
should not interfere with your use of the database.

John W. Vinson [MVP]

 

[Guest]

Thank you. I won't have a chance to check for a while, but suspect that's
it!!!

 

[Guest]

Thank you. I'll double check, but suspect David spotted my error.