Can't sort table via ID

[waq963]

Hi, Basically i am using MS Access 2007 and a custom ID formatted as Text and it goes as follows: Q1, Q2.......Q60. So from Q1 to 60. But it sorts the field with all the ID's that contain 1 first e.g. Q1,Q10,Q11 etc. The sort function does not sort it in increments. Can you help me please?

 

[Rabbie]

That's because your IDs are text and not numbers. If you need them in numeric sequence you will need to store the numeric part in a separate numeric field and sort on that.

 

[waq963]

Thanks for the reply. I thought there may be an easier way. Going to have to go with what you said. Cheers.

 

[DCrake]

Alternatively if you would have stored the id's as Q01,Q02,Q03, etc it would have worked ok

 

[Rabbie]

Alternatively if you would have stored the id's as Q01,Q02,Q03, etc it would have worked ok

But for that to work you need to know inadvance what the maximum value will be. As it is currently written Q100 will be sorted before Q11. so then you will have to use Q001,.....Q010.Q011....... Q100. This will work until you reach Q1000. You see the problem?

 

[DCrake]

Of course I sqw the problem, but if you read the original post it only referred to Q1 to Q60. Now who's being pedantic?

I personally would have started at Q0001

 

[Rabbie]

. Now who's being pedantic?

Me! Tho' I prefer prefer to call it a need for accuracy.

I posted the correction incase some one else with a similar problem to the OP but more records read the post and so could get their design correct. I have noticed many of our experts here repeatedly urge people not to have sorting keys of the type Q001 etc but to split the fields. Integer sorts are quicker than string sorts and although with small recordsets this difference may be trivial in larger sets it can be noticable.

 

[gemma-the-husky]

the thing is, people start off not being experts in databases, and most likely model their structure on their excel spreadsheets.

i am sure we all look back on projects that we would have done differently with hindsight

 

[Rabbie]

the thing is, people start off not being experts in databases, and most likely model their structure on their excel spreadsheets.

i am sure we all look back on projects that we would have done differently with hindsight

I quite agree. The best way to learn is to build a prototype and see how you can improve it.

Too much Excel experience can be a disadvantage as it makes it more difficult to get your head around Normalization.

 

[Atomic Shrimp]

Not to negate anything anyone said above about sensible DB design, you can sort it like this:

SELECT MyTable.MyCustomIDField, MyTable.AField, MyTable.AnotherField
FROM MyTable
ORDER BY Val(Mid([MyCustomIDField],2));

 

[Rabbie]

Not to negate anything anyone said above about sensible DB design, you can sort it like this:

SELECT MyTable.MyCustomIDField, MyTable.AField, MyTable.AnotherField
FROM MyTable
ORDER BY Val(Mid([MyCustomIDField],2));

That doesn't work with OP's original data. Q1 will sort after Q10 using your method.

 

[Dennisk]

why not strip off the 'Q' in a query, convert to Integer and sort that.

 

[Atomic Shrimp]

That doesn't work with OP's original data. Q1 will sort after Q10 using your method.

It works. The Mid function strips off the Q and the Val function converts the remaining bit to a number - and 10 is greater than 1, so Q10 comes after Q1.

 

[Rabbie]

It works. The Val function converts it to a number - and 10 is greater than 1

My apologies. I didn't look carefully enough