Format for number field

[Corinne]

I have a database that stores pupil information. One of
the fields in the table is for reading ages of the
pupils. I can't seem to get the format right to store the
ages so that they will sort in age order. If I put in
6.10 this would mean the pupil has a reading age of 6
years 10 months. I have tried currency as that seems the
nearest format but I get the £ sign in front, which I
don't need. In addition to all this a few of the ages are
recorded as + or - if a pupil has a reading age of more
than 11 it would be recorded as 11.00+. Has anybody got a
solution to this problem?
Thanks in advance for any help.

 

[Jeff Boyce]

Corinne

Since you are trying to store "information", not data, consider using a text
field. If you try to store 6 years, 10 months as a numeric (6.10), you'd
have to allow/accept nonsense values like 6.35 or 6.99. And if you insist
on including a "more or less" indicator (your "+/-"), ...

Of course, you don't HAVE to store all that in a single field -- you could
use three (years, months, +/-/empty). If that seems too difficult, consider
storing in a text field and building a parsing function that translates text
into numbers.

Seriously though, how did you plan to calculate an "average" reading age
when you included the "+/-"? If you aren't intending to do any math with
the numbers, you don't need numeric data types.

Good luck

Jeff Boyce
<Access MVP>

 

[Chadlon]

Hi Corinne,

Would your problem become any easier if you 'decouple' the way data is
presented and the way it is stored?
For example, if the 'granularity' of your reading age is 1 month, you could
represent the data internally as integer months and allow the data to be
displayed/specified using a combo-box.

So, externally the value is shown as "6.10", internally you could have it as
82.
And the external value "6.9" would be stored as 81.
And the external value "11+" could be stored as 145 or 150 (or whatever).
You get the idea.

Then your sort problem goes away, since sorting on the internal
representation delivers the required sequence - I suspect.

You have a separate problem regarding the 'policing' of this data ... I
suspect it represents a 'snapshot' at a point in time, so it is likely that
it is necessary to store it in a record subordinate to Pupil / Student - and
not in the actual Pupil record itself. A record like Pupil Year or Pupil
Term would be a happier home for it.

Good Luck
CD.



I have a database that stores pupil information. One of
the fields in the table is for reading ages of the
pupils. I can't seem to get the format right to store the
ages so that they will sort in age order. If I put in
6.10 this would mean the pupil has a reading age of 6
years 10 months. I have tried currency as that seems the
nearest format but I get the £ sign in front, which I
don't need. In addition to all this a few of the ages are
recorded as + or - if a pupil has a reading age of more
than 11 it would be recorded as 11.00+. Has anybody got a
solution to this problem?
Thanks in advance for any help.

 

[Corinne]

-----Original Message-----

Three seperate fields may be an option although it may
make it more difficult to input the data quickly. Could I
then join them together so they looked like one field on
a report. I have done this with Surname and First name so
would the theory be the same.

Since you are trying to store "information", not data, consider using a text
field.

I have the data stored as text at the moment but the
problem with that is if I am asked for all pupils with a
reading age of less than 7 the query doesn't work.

Of course, you don't HAVE to store all that in a single field -- you could
use three (years, months, +/-/empty). If that seems too difficult, consider
storing in a text field and building a parsing function that translates text
into numbers.

Sorry I don't understand the phrase 'parsing function'
could you explain it to me?

Seriously though, how did you plan to calculate

an "average" reading age

I can't forsee a need to calculate average reading ages
although in time to come someone may ask if I can give
them that information. You never know what people need
until they ask for it.

 

[Jeff Boyce]

Corinne

I've been out of town... sorry for the delay. I took a look at Chadlon's
suggestion and like it (but hadn't offered it because your original post was
specific about including "+/-".

If you data is stored as text, you'll have problems looking up "less than
7". If your data is stored as data (i.e., numbers), you won't. So, let me
pose my same question again ... if you wanted to store seven years, zero
months, "minus", would you want to find it in your "less than 7" query?

Good luck

Jeff Boyce
<Access MVP>