DataManipulator
asked on
Order Alphanumeric grouped records numerically
I have a report (CR 8.0.1.0) printing out details of multiple people living at street addresses. Group 2 is the street. Group 3 is the address formula consists of four data fields: number; letter suffix; half; unit. i.e. 97A Main St or 125 Washington Ave Unit 17. I need the order of Group 3 to have numeric priority (1, 5, 14-A, 33, 275, 4200) rather than alphanumeric (1, 14-A, 275, 33, 4200, 5).
I have tried ordering by fields using the ORDER clause in Show SQL Query. This seems to do nothing. I have tried the various options in Change Group Options. These mostly deal with the formula as alphanumeric. Record Sort Order will not allow me to remove or reorder groups.
How can I order these street addresses in numeric (street) order?
Thanks,
DM
I have tried ordering by fields using the ORDER clause in Show SQL Query. This seems to do nothing. I have tried the various options in Change Group Options. These mostly deal with the formula as alphanumeric. Record Sort Order will not allow me to remove or reorder groups.
How can I order these street addresses in numeric (street) order?
Thanks,
DM
EYoung
What I have done several times for that is to create a separate "numeric" field in the table containing just the numeric portion of the address. Then use it in the "Order By" clause and in the grouping. That will do the trick and your report will present the data correctly.
What I have done several times for that is to create a separate "numeric" field in the table containing just the numeric portion of the address. Then use it in the "Order By" clause and in the grouping. That will do the trick and your report will present the data correctly.
ASKER
EYoung, There already is a seperate numeric field in the raw data. That is part of the formula used in Group 3 and what seems to be taking precident. The first thing I tried was redoing the ORDER BY, but nothing changes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
EYoung, the numeric field is the first part of the formula used by Group 3. If there is no better answer, I will have to move Group 3 to 4 and have the new Group 3 be just the numeric value. That is of course if an integer data field is sorted numerically rather alpha-numerically. DM
ASKER
EYoung, the numeric field is the first part of the formula used by Group 3. If there is no better answer, I will have to move Group 3 to 4 and have the new Group 3 be just the numeric value. That is of course if an integer data field is sorted numerically rather alpha-numerically. DM
Yes, you might try that. I am leaving for the evening. Will check back with you tomorrow.
ASKER
EYoung, the group ordering taking precedence was the key. I did add another group and resorted the group order so that the numeric came alpha.
One note, I had to create a formula containing:
IIF ({person.half}='5',{person
Using this rather than just the number field makes sure a 1/2 address comes after a whole. i.e. 14, 14 1/2
One note, I had to create a formula containing:
IIF ({person.half}='5',{person
Using this rather than just the number field makes sure a 1/2 address comes after a whole. i.e. 14, 14 1/2
ASKER
Thanks for pointing me in the right direction.
DM
DM
You're welcome. Sorry to be getting back to you so late in the day. (Gulp - I totally forgot.)