Number format help needed please

[Corinne]

Hi
My database contains the data for special needs pupils. I have a field that
has the reading ages of the pupils taken from a reading test. I struggled
for a long time to come up with a format that would record the data in the
way I wanted. E.g. a pupil with a reading age of 6 years 8 months is
recorded as 6.08, 10 years 11 months is 10.11. This worked fine as all the
staff are aware of the format as this is how the results come through from
the exam board. The format I used is Data Type - Currency, Format -
Standard, Decimal places - 2. This way a reading age of 6years10 months is
shown as 6.10, also I could sort the data in ascending/descending order,
which when we started out was perfect.
Now (2 years later) we have been asked to produce graphs of the average
reading age of the pupils in each class. I can do this, but of course the
format I have used for the numbers doesn't work out right on the average
sum.
So what I need to know is where to start so that I can have a format of
years and months in a field that will add up and average out to suit the
graphs. I appreciate I may have to in put the data again but that isn't too
much of a problem.I could put in the data as a number in just months (10
years 6 months = 126) but then on the reports this would look odd and staff
are not used to seeing the reading age in this way.
If anybody has any ideas that might go some way to sorting this problem I
would be grateful becuase I am going grey over this.
Many Thanks
Corinne

 

[Ted]

Hi Corrine,

In a query you could multiply the integer part of the
number by 12, then add to the decimal portion multiplied
by 100 to get the age in months. This could be summed,
then divided by 12 to get years, or could be divided by
12 initially to get age in decimal years.

Following are some examples that would convert your age
format to age in months. These could be simpler if you
find a function that returns only the decimal part of a
number, but I couldn't think of one off hand, so I am
just subtracting the integer part from the original
number to get the decimal part.

AgeMonths: Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100

Similarly you could have:

AgeYears: (Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100)/12

I don't think that you would need to change the way that
you store your numbers since they are otherwise working
well for you and this calculation would be easy to build
into queries, forms, and reports when needed.

Hope that helps.

-Ted

 

[Corinne]

Thanks for the help Ted, but at the moment I am still having problems. I
copied your equation into the next blank space in the query and I get an
error that tells me I have entered an operand without an operator. This is
something I just don't understand. I take it I have put the equation in the
right place, I made sure my Reading Age field was in the query. So I am not
sure what has gone wrong.
I did try a few things myself and if I add a two more brackets between the
second Reading age, as shown below

AgeYears: (Fix([ReadingAge])*12 +([ReadingAge])-(Fix[ReadingAge])*100)/12

I do get a number but it is a strange one. 8.00 converts to -58, 7.11
converts to -50.7408333
Any further ideas?

Corinne

Hi Corrine,

In a query you could multiply the integer part of the
number by 12, then add to the decimal portion multiplied
by 100 to get the age in months. This could be summed,
then divided by 12 to get years, or could be divided by
12 initially to get age in decimal years.

Following are some examples that would convert your age
format to age in months. These could be simpler if you
find a function that returns only the decimal part of a
number, but I couldn't think of one off hand, so I am
just subtracting the integer part from the original
number to get the decimal part.

AgeMonths: Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100

Similarly you could have:

AgeYears: (Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100)/12

I don't think that you would need to change the way that
you store your numbers since they are otherwise working
well for you and this calculation would be easy to build
into queries, forms, and reports when needed.

Hope that helps.

-Ted

-----Original Message-----
Hi
My database contains the data for special needs pupils. I have a field that
has the reading ages of the pupils taken from a reading test. I struggled
for a long time to come up with a format that would record the data in the
way I wanted. E.g. a pupil with a reading age of 6 years 8 months is
recorded as 6.08, 10 years 11 months is 10.11. This worked fine as all the
staff are aware of the format as this is how the results come through from
the exam board. The format I used is Data Type - Currency, Format -
Standard, Decimal places - 2. This way a reading age of 6years10 months is
shown as 6.10, also I could sort the data in ascending/descending order,
which when we started out was perfect.
Now (2 years later) we have been asked to produce graphs of the average
reading age of the pupils in each class. I can do this, but of course the
format I have used for the numbers doesn't work out right on the average
sum.
So what I need to know is where to start so that I can have a format of
years and months in a field that will add up and average out to suit the
graphs. I appreciate I may have to in put the data again but that isn't too
much of a problem.I could put in the data as a number in just months (10
years 6 months = 126) but then on the reports this would look odd and staff
are not used to seeing the reading age in this way.
If anybody has any ideas that might go some way to sorting this problem I
would be grateful becuase I am going grey over this.
Many Thanks
Corinne


.

 

[Mae]

I am just a user, not an MVP, but one way that you could
do this is with two different fields: ReadingAgeYears and
ReadingAgeMonths. For graphs and other calculations, use
ReadingAgeYears * 12 + ReadingAgeMonths. You can still
display it how your staff is used to seeing it. In the
long run, however, it might be wise to change from the
decimal system you're currently using. My thought is that
a decimal should mean tenths (or hundredths, etc). Making
it mean something else can get confusing, and as you've
seen, will not result in accurate calculations. Even
changing it to a comma or a dash, so that 6 years and 8
months is shown as 6,8 or 6-8 (although that might look
like a date) would be less ambiguous.

The important point is that you must store the data
accurately in order to get good figures, but you can
_display_ the data any way you want in a report. Since
you can no longer enter the ages as 6.08, however, there
really is no reason that you need to display it that way.
Your staff would have no problem getting used to
seeing "6,8" or even "6 yrs 8 mos" on a report, and it
would make much more sense to anyone outside of your team
who might be looking at it.

.... Just some thoughts from a non-MVP...

(Also, you _could_ store the reading age in just one
field, as months, and then manipulate formats and
calculations to display it in the current manner. If the
test gives you months, that might be the easier way. If
the test gives you years and months, however, you would
have to do calculations to get the number of months, which
you would then have to un-calculate to get back to the
years and months, so there's no sense in doing it that
way!)

 

[Corinne]

Thanks for the help Mae, I ended up sending the table to excel, doing a text
to columns thing to change it to years and months in two different fields,
then I sent the table back to Access. I managed to get a calculated field on
the query using the Reading ages months and years and the correct data came
up. Which is great, the problem I now have is that when I add data from the
rest of the table, such as the tutor group I end up with a tye mismatch
error. Do you have any ideas on this?

 

[Mae]

What kind of data is going into the tutor group? Does
this relate to the reading age fields, or is is something
different?

I signing off for the night, maybe someone else will have
some ideas. I will check back tomorrow, as I'm curious too.

Glad you got the age thing to come out right.

 

[Ted]

Hi Corrine,

I'm so sorry, I should have checked the formula more
carefully after I typed it. Try the following (hopefully
I got it right this time).

AgeYears:
(Fix([ReadingAge])*12 +
([ReadingAge]- Fix([ReadingAge]))*100)/12

By the way, to give a little further explanation, the
Fix() function strips the decimal portion from the number
within the ()'s. The other ()'s within the formula are
to make sure that the math operations are carried out in
the correct order. The reason your formula was getting a
negative result was that the 100 was being multiplied by
the negative integer portion before being subtracted from
the original number. It should have been subracting the
integer portion from the number, then muliplying by 100.

Sorry you had to resort to exporting to excel in the
meantime.

-Ted

-----Original Message-----
Thanks for the help Ted, but at the moment I am still having problems. I
copied your equation into the next blank space in the query and I get an
error that tells me I have entered an operand without an operator. This is
something I just don't understand. I take it I have put the equation in the
right place, I made sure my Reading Age field was in the query. So I am not
sure what has gone wrong.
I did try a few things myself and if I add a two more brackets between the
second Reading age, as shown below

AgeYears: (Fix([ReadingAge])*12 +([ReadingAge])-(Fix [ReadingAge])*100)/12

I do get a number but it is a strange one. 8.00 converts to -58, 7.11
converts to -50.7408333
Any further ideas?

Corinne

Hi Corrine,

In a query you could multiply the integer part of the
number by 12, then add to the decimal portion multiplied
by 100 to get the age in months. This could be summed,
then divided by 12 to get years, or could be divided by
12 initially to get age in decimal years.

Following are some examples that would convert your age
format to age in months. These could be simpler if you
find a function that returns only the decimal part of a
number, but I couldn't think of one off hand, so I am
just subtracting the integer part from the original
number to get the decimal part.

AgeMonths: Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100

Similarly you could have:

AgeYears: (Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100)/12

I don't think that you would need to change the way that
you store your numbers since they are otherwise working
well for you and this calculation would be easy to build
into queries, forms, and reports when needed.

Hope that helps.

-Ted

-----Original Message-----
Hi
My database contains the data for special needs

pupils.
I have a field that

has the reading ages of the pupils taken from a

reading
test. I struggled

for a long time to come up with a format that would record the data in the
way I wanted. E.g. a pupil with a reading age of 6

years
8 months is

recorded as 6.08, 10 years 11 months is 10.11. This worked fine as all the
staff are aware of the format as this is how the

results
come through from

the exam board. The format I used is Data Type - Currency, Format -
Standard, Decimal places - 2. This way a reading age

of
6years10 months is

shown as 6.10, also I could sort the data in ascending/descending order,
which when we started out was perfect.
Now (2 years later) we have been asked to produce

graphs
of the average

reading age of the pupils in each class. I can do

this,
but of course the

format I have used for the numbers doesn't work out right on the average
sum.
So what I need to know is where to start so that I can have a format of
years and months in a field that will add up and

average
out to suit the

graphs. I appreciate I may have to in put the data

again
but that isn't too

much of a problem.I could put in the data as a number

in
just months (10

years 6 months = 126) but then on the reports this

would
look odd and staff

are not used to seeing the reading age in this way.
If anybody has any ideas that might go some way to sorting this problem I
would be grateful becuase I am going grey over this.
Many Thanks
Corinne


.

.

 

[Corinne]

Thanks Ted That worked. Saved a lot of moving to and fro in excel. I am
grateful for your time and help.

Corinne

Hi Corrine,

I'm so sorry, I should have checked the formula more
carefully after I typed it. Try the following (hopefully
I got it right this time).

AgeYears:
(Fix([ReadingAge])*12 +
([ReadingAge]- Fix([ReadingAge]))*100)/12

By the way, to give a little further explanation, the
Fix() function strips the decimal portion from the number
within the ()'s. The other ()'s within the formula are
to make sure that the math operations are carried out in
the correct order. The reason your formula was getting a
negative result was that the 100 was being multiplied by
the negative integer portion before being subtracted from
the original number. It should have been subracting the
integer portion from the number, then muliplying by 100.

Sorry you had to resort to exporting to excel in the
meantime.

-Ted

-----Original Message-----
Thanks for the help Ted, but at the moment I am still having problems. I
copied your equation into the next blank space in the query and I get an
error that tells me I have entered an operand without an operator. This is
something I just don't understand. I take it I have put the equation in the
right place, I made sure my Reading Age field was in the query. So I am not
sure what has gone wrong.
I did try a few things myself and if I add a two more brackets between the
second Reading age, as shown below

AgeYears: (Fix([ReadingAge])*12 +([ReadingAge])-(Fix [ReadingAge])*100)/12

I do get a number but it is a strange one. 8.00 converts to -58, 7.11
converts to -50.7408333
Any further ideas?

Corinne

Hi Corrine,

In a query you could multiply the integer part of the
number by 12, then add to the decimal portion multiplied
by 100 to get the age in months. This could be summed,
then divided by 12 to get years, or could be divided by
12 initially to get age in decimal years.

Following are some examples that would convert your age
format to age in months. These could be simpler if you
find a function that returns only the decimal part of a
number, but I couldn't think of one off hand, so I am
just subtracting the integer part from the original
number to get the decimal part.

AgeMonths: Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100

Similarly you could have:

AgeYears: (Fix([ReadingAge])*12 + ([ReadingAge]-Fix
[ReadingAge])*100)/12

I don't think that you would need to change the way that
you store your numbers since they are otherwise working
well for you and this calculation would be easy to build
into queries, forms, and reports when needed.

Hope that helps.

-Ted
-----Original Message-----
Hi
My database contains the data for special needs pupils.
I have a field that
has the reading ages of the pupils taken from a reading
test. I struggled
for a long time to come up with a format that would
record the data in the
way I wanted. E.g. a pupil with a reading age of 6 years
8 months is
recorded as 6.08, 10 years 11 months is 10.11. This
worked fine as all the
staff are aware of the format as this is how the results
come through from
the exam board. The format I used is Data Type -
Currency, Format -
Standard, Decimal places - 2. This way a reading age of
6years10 months is
shown as 6.10, also I could sort the data in
ascending/descending order,
which when we started out was perfect.
Now (2 years later) we have been asked to produce graphs
of the average
reading age of the pupils in each class. I can do this,
but of course the
format I have used for the numbers doesn't work out
right on the average
sum.
So what I need to know is where to start so that I can
have a format of
years and months in a field that will add up and average
out to suit the
graphs. I appreciate I may have to in put the data again
but that isn't too
much of a problem.I could put in the data as a number in
just months (10
years 6 months = 126) but then on the reports this would
look odd and staff
are not used to seeing the reading age in this way.
If anybody has any ideas that might go some way to
sorting this problem I
would be grateful becuase I am going grey over this.
Many Thanks
Corinne


.

.

 

[Corinne]

Mae, see Ted's 2nd reply this worked. The other fields I need for the chart
are in different tables and mainly text. They all are related, I am not sure
what the problem is but I will sort it eventually. It is just a matter of
working through the problem and trying different things.
Thanks for your help though,you gave me a different view on things.