Sorting Values

[Francois]

Please help. I have a field containing values ex. 1/01/0228, 2/01/2008,
3/01/2008. When i get to 10/01/1008 and 11/01/2008. Access sort it as follow:
01/01/2008, 10/01/2008, 11/01/2008, 2/01/2008, 3/01/2008. How can i get the
sorting to stay 1/012008, 2/01/2008 3/01/2008..........10/01/2008, 11/01/2008?

 

[Tom Wickerath]

Hi Francois,

Is the data type for your field set as Text? It appears as if your data
represents dates, although some of the years look a bit suspicious...

Try changing the data type to Date / Time

Note:
You may not have success making this change if you have data that is not a
valid date, in which case you can run a query to locate the offending records
to edit them manually.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Francois]

Hi Tom

No It is not dates. The values in the filed is file numbers the first number
01 would be the record number the second part 01 woulld be the month and 2008
would be the year. So 01/01/2008 can go to 67/01/2008.

I made some finger errors in the initial post.

It should read 01/01/2008, 02/01/2008, 03/01/2008, ...............,
10/01/2008, 11/01/2008. As soon as a close the DB Acces switch the sort then
to 01/01/2008, 10/01/2008, 11/01/2008, 02/01/2008, 20/01/2008, 21/01/2008,
03/01/2008, 30/01/2008.

 

[Tom Wickerath]

Hi Francois,

Your data is sorting as expected for a text data type. I don't know your
data well enough, but I'm left wondering if you should record your record
number in a separate field. Your current data does not appear to be stored
atomically (ie. in the simplist form).

What about the possibility of storing a record number in a field with data
type Number, and the date in a Date / Time data type? Unless, of course, if
record numbers can include alphanumeric values, such as 67b.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Francois]

Hi Tom

I think you are correct this also my primary key. maybe i should insert a
additional field as aprimary field 1,2,3,4,5,6,7,8,9........ and make the
other a separate field.

 

[Tom Wickerath]

I think you are correct this also my primary key.

By default, a table will sort in primary key order. This explains your
earlier observation that when you close the table and re-open it, your
records are not in the desired order.

I personally like using autonumber (surrogate) primary keys myself. However,
such keys should be considered *meaningless*.

...and make the other a separate field.

Data should be stored in the simplist form possible. One should not attempt
to store multiple values in a single field. This is called a multivalued
field design, and is generally not considered good database design. You might
want to read two papers by Database Design expert Michael Hernandez,
available here:

http://www.accessmvp.com/JConrad/accessjunkie/DatabaseDesign101

The Database Design Tips paper is a very easy read--it is only 4 pages in
length (about one cup of coffee for me <smile>).

I'm going off-line now.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

Tom:

I don't think so, e.g. "02/01/2008" > "11/01/2008" = False. It looks to me
like Francois is probably storing the values without the leading zeros and
formatting the column to show them. This might arise from the use of an
inappropriate input mask. Probably the best approach would be to separate
the serial record numbers, the month and year values all as integer data
types and sort by year, month, record number.

Ken Sheridan
Stafford, England

 

[Tom Wickerath]

Hi Ken,

You could be right. I was focusing more on the sort order indicated in the
first post, which, with one exception in the result, does not include the
leading zeros. In any case, I think we both agree that this multivalued data
should be split into separate fields, if possible, assuming it would not
cause too much rework for the rest of the application.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[emre]

muregsaemrullah

Hi Tom

I think you are correct this also my primary key. maybe i should insert a
additional field as aprimary field 1,2,3,4,5,6,7,8,9........ and make the
other a separate field.

 

[emre]

emrullah

Hi Tom

I think you are correct this also my primary key. maybe i should insert a
additional field as aprimary field 1,2,3,4,5,6,7,8,9........ and make the
other a separate field.