If you have ExegesIS Version 2 and a spare user-defined field, you could use
the latter as a numeric sort field. I did this in the Event table, so that I
could sort numerically on our SOU site codes in the External Ref No field.
Amanda Bodily's instructions for changing a user-defined field to numeric
are given below, copied from her email (note that the change to the design
MUST be made in the smrdata.mdb database, not the "main" database smr.mdb
which is linked to the former). Perhaps Tony could confirm that this method
is OK, and also any restrictions on what fields can be altered in the
smrdata.mdb design view? This method avoids the need to add possibly
unwanted leading zeros to our reference numbers as Tony suggests.
Amanda's instructions:
"To do this open the database as administrator go to adhoc queries, go to
file > open database and find the smrdata.mdb file. Once this has been
opened highlight the table event and
open it in design view. Highlight the field user1 and if you click in the
field that says 'text' a little pull down arrow should appear and you can
select number (it will default to Long integer which is fine). Then close
the database and open the smr.mdb file it should go straight to the main
menu. Go to adhoc queries** and add in a title to the eventuser1= line and
then that field will appear on the form. the only thing to be wary of in a
couple of years time when the data is upgraded to another system ie
office2002?? then you will have to remember to go and change that field
again, the data won't be lost but the field will go back to being text!"
(**I think Amanda meant "go to Edit Ini file")
You then do a bulk copying of the data from the prefref field to your
numeric sort field in table view, via Adhoc Queries. To do this, highlight
the prefref column (by clicking on the top of the column), choose Edit-Copy,
highlight the user-defined field and choose Edit-Paste.
I had the additional problem that the field I wanted to sort numerically
contained a letter prefix. I got round this in a rather complicated way,
but does anyone know what expression to put in an update query in order to
fill a numeric field with a number from another field without it's letter
prefix?
I don't know why numeric sorting on the UID, External Ref No and PrefRef
fields isn't built into the forms and indexes, let alone the reports, as
this seems to me one of the obvious ways we'd want to arrange and view data.
Ingrid Peckham,
Southampton City SMR,
[log in to unmask]
-----Original Message-----
From: Emma Jones [mailto:[log in to unmask]]
Sent: 03 October 2001 09:30
To: [log in to unmask]
Subject: Re: Exegesis - printing in sorted SMR number order
Dear Tony
Our problem is that sorting on the PrefRef (a text field) does not produce
a numerically ordered printout. I have tried to change the format of the
PrefRef field to numerical, as the field contains only numbers, but the
system does not let me do this.
I there another way of producing a report in PrefRef number order?
Emma Jones
Warwickshire SMR
|