 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Sorting Values
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? 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 __________________________________________ > 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? 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. > Hi Francois, > [quoted text clipped - 19 lines] > > 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? 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 __________________________________________ > Hi Tom > [quoted text clipped - 8 lines] > 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. 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. > Hi Francois, > [quoted text clipped - 25 lines] > > 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. > 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 __________________________________________ > 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. muregsaemrullah > Hi Tom > [quoted text clipped - 38 lines] >> > 21/01/2008, >> > 03/01/2008, 30/01/2008. emrullah > Hi Tom > [quoted text clipped - 38 lines] >> > 21/01/2008, >> > 03/01/2008, 30/01/2008. Tom: >>Your data is sorting as expected for a text data type<< 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 > Hi Francois, > [quoted text clipped - 25 lines] > > 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. 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 __________________________________________ > Tom: > [quoted text clipped - 9 lines] > Ken Sheridan > Stafford, England |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.