Percentages in datasheets

[Guest]

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)

 

[Guest]

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Set the properties as "Fixed" - Then - set the decimals 0

Hope this helps


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )



:

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Smartin]

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)

I'm sure you meant

Percent([ActualScoreField]/[PosibleScoreField])*100)

(^:

 

[Guest]

Ha Ha

Yeah of course you are right. Been a (very) long time since I was sat in a
maths lesson at school


--
Wayne
Manchester, England.
2 things to remember:
Enjoy whatever it is you do and the F1 Key doesn’t bite.

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)

I'm sure you meant

Percent([ActualScoreField]/[PosibleScoreField])*100)

(^:

 

[Smartin]

Ha Ha

Yeah of course you are right. Been a (very) long time since I was sat in a
maths lesson at school

<G> but if it gets you 150% on your tests, so much the better!

 

[Guest]

Hi Jody.

If you are still having trouble with the fomatting, try eliminating the
*100 from the formula that calculates the percentage. Then, set the
Format property of that column to "Percent", and the Decimal Places property
to zero.

Additionally, if you place the following function into a module, you should
be able to add a column to your query like this:
Grade: fGrade([ActualScoreField]/[PossibleScoreField])
which will give you the letter grade.

Public Function fGrade(Percentage As Double) As String
Select Case Percentage
Case Is > 1
fGrade = "A+"
Case 0.97 To 1
fGrade = "A+"
Case 0.93 To 0.97
fGrade = "A"
Case 0.9 To 0.93
fGrade = "A-"
Case 0.87 To 0.9
fGrade = "B+"
Case 0.83 To 0.87
fGrade = "B"
Case 0.8 To 0.83
fGrade = "B-"
Case 0.77 To 0.8
fGrade = "C+"
Case 0.73 To 0.77
fGrade = "C"
Case 0.7 To 0.73
fGrade = "C-"
Case 0.67 To 0.7
fGrade = "D+"
Case 0.63 To 0.67
fGrade = "D"
Case 0.6 To 0.63
fGrade = "D-"
Case Else
fGrade = "F"
End Select
End Function


-Michael

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )



:

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Michael,

Oh my goodness!! I'm very happy about this. This will make my life easier.
I was beginning to think that this wasn't possible. I really appreciate the
time you took to type this out for me.

I would have replied earlier, but I didn't get a message that there was a
reply on this message board so I wasn't checking. I'm so glad I did.

OK, I have a fairly easy question (I think). I did a query that put the
subjects together (instead of showing each paper's grade), added up both the
possible scores and the actual scores, an showed the total percentage for
each subject, kind of like a report card. For some reason, it quit working
and no matter what I do, I cannot get it to work again. Do you know how to
do this? And if so, would it be easy to put the actual letter grade in the
query also?

Thank you,
Jody )

Hi Jody.

If you are still having trouble with the fomatting, try eliminating the
*100 from the formula that calculates the percentage. Then, set the
Format property of that column to "Percent", and the Decimal Places property
to zero.

Additionally, if you place the following function into a module, you should
be able to add a column to your query like this:
Grade: fGrade([ActualScoreField]/[PossibleScoreField])
which will give you the letter grade.

Public Function fGrade(Percentage As Double) As String
Select Case Percentage
Case Is > 1
fGrade = "A+"
Case 0.97 To 1
fGrade = "A+"
Case 0.93 To 0.97
fGrade = "A"
Case 0.9 To 0.93
fGrade = "A-"
Case 0.87 To 0.9
fGrade = "B+"
Case 0.83 To 0.87
fGrade = "B"
Case 0.8 To 0.83
fGrade = "B-"
Case 0.77 To 0.8
fGrade = "C+"
Case 0.73 To 0.77
fGrade = "C"
Case 0.7 To 0.73
fGrade = "C-"
Case 0.67 To 0.7
fGrade = "D+"
Case 0.63 To 0.67
fGrade = "D"
Case 0.6 To 0.63
fGrade = "D-"
Case Else
fGrade = "F"
End Select
End Function


-Michael

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )



:

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Jody,

I'm glad I was able to help. But, telling me "it quit working" is not
giving me much information to go on (imagine providing only that bit of info
to your mechanic and expecting him to tell you how to fix your car). So, if
you'll provide the structure of the table(s) involved, along with the SQL of
the query, I'll be happy to take a look. If you were also to provide sample
data from the table(s), along with the results you expect the query to
return, that would make things even easier.

As for showing a letter grade in the Totals query, that's easy, since the
function I provided can be used pretty much anywhere you want. It will
accept any decimal value and return a letter grade. So, as long as your
query is calculating the percentage as a decimal (and not multiplying it by
100 as before), you can feed that value to the function and get a letter
grade. It doesn't matter whether you do as I suggested below, and feed the
function a calculation that will result in a percentage, or feed it the
actual decimal number itself.

In other words, let's imagine you have some fields in your query named
TotalPossible (total number of points possible), TotalActual (total number of
actual points), and TotalPercentage, which is a calculated field equal to
TotalActual divided by TotalPossible. You could then add a field to your
query using either of the two methods below, and get the same result:

TotalGrade: fGrade([TotalActual]/[TotalPossible])
TotalGrade: fGrade([TotalPercentage])

-Michael

Michael,

Oh my goodness!! I'm very happy about this. This will make my life easier.
I was beginning to think that this wasn't possible. I really appreciate the
time you took to type this out for me.

I would have replied earlier, but I didn't get a message that there was a
reply on this message board so I wasn't checking. I'm so glad I did.

OK, I have a fairly easy question (I think). I did a query that put the
subjects together (instead of showing each paper's grade), added up both the
possible scores and the actual scores, an showed the total percentage for
each subject, kind of like a report card. For some reason, it quit working
and no matter what I do, I cannot get it to work again. Do you know how to
do this? And if so, would it be easy to put the actual letter grade in the
query also?

Thank you,
Jody )

Hi Jody.

If you are still having trouble with the fomatting, try eliminating the
*100 from the formula that calculates the percentage. Then, set the
Format property of that column to "Percent", and the Decimal Places property
to zero.

Additionally, if you place the following function into a module, you should
be able to add a column to your query like this:
Grade: fGrade([ActualScoreField]/[PossibleScoreField])
which will give you the letter grade.

Public Function fGrade(Percentage As Double) As String
Select Case Percentage
Case Is > 1
fGrade = "A+"
Case 0.97 To 1
fGrade = "A+"
Case 0.93 To 0.97
fGrade = "A"
Case 0.9 To 0.93
fGrade = "A-"
Case 0.87 To 0.9
fGrade = "B+"
Case 0.83 To 0.87
fGrade = "B"
Case 0.8 To 0.83
fGrade = "B-"
Case 0.77 To 0.8
fGrade = "C+"
Case 0.73 To 0.77
fGrade = "C"
Case 0.7 To 0.73
fGrade = "C-"
Case 0.67 To 0.7
fGrade = "D+"
Case 0.63 To 0.67
fGrade = "D"
Case 0.6 To 0.63
fGrade = "D-"
Case Else
fGrade = "F"
End Select
End Function


-Michael

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

:

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )



:

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Michael,

I'm sorry for not explaining myself well enough. I try, but often fail.

Here's the SQL that I put together since your advice (I've already deleted
what I had before because I kept getting an error):

SELECT [qry-GradeBook].Subject, [qry-GradeBook].Score,
[qry-GradeBook].Possible, (([Score]/[Possible])) AS [Percent],
fGrade([Score]/[Possible]) AS TotalGrade
FROM [qry-GradeBook];

I had it working so that the it added up all the Math, Spelling, English,
etc. as a progress report for each subject total and not just individual
worksheet grades for each subject.

From the grading key that I put into the module, I was getting an error on
one of the grades. It was showing an error by putting a grade in as an A
when it should have been an A+. I know this might seem petty, but my
daughter gets more excited about an A+, then just an A. Anyway, I'm thinking
it's because it falls between the percentages. It was 96.875. I'd already
put percentage in the properties and zero decimals, but it didn't read that
way as the letter grade is pulled from the grading key.

I decided to try to add decimal points to the grading key in the module.
Here that is below:

Public Function fGrade(Percentage As Double) As String
Select Case Percentage
Case Is > 1
fGrade = "A+"
Case 0.965 To 1
fGrade = "A+"
Case 0.925 To 0.964
fGrade = "A"
Case 0.895 To 0.924
fGrade = "A-"
Case 0.865 To 0.894
fGrade = "B+"
Case 0.825 To 0.864
fGrade = "B"
Case 0.795 To 0.824
fGrade = "B-"
Case 0.765 To 0.794
fGrade = "C+"
Case 0.725 To 0.764
fGrade = "C"
Case 0.695 To 0.724
fGrade = "C-"
Case 0.665 To 0.694
fGrade = "D+"
Case 0.625 To 0.664
fGrade = "D"
Case 0.595 To 0.624
fGrade = "D-"

Case Else
fGrade = "F"
End Select
End Function


This actually fixed it so the A was now an A+ like it should have been, but
now it's causing another error. It's making a grade that should be an B+ an
F. I think because it's not within any range. The percentage is
89.4736842105263.

Do you know how to make the query recongize the rounded up number that I've
got showing so that it doesn't do this?

Did this make sense or am have I completely confused you?

Also, this isn't that big of a deal, but where I don't enter a
score/possible, it shows #Error in the letter grade field. I tried to fix
this by putting in zeros, but it still gives me an error. I don't mind this,
but I just didn't know if this will cause more errors down the road.

If it's easier for you to see it, I could compress the database and send it
to you. It's not that large or complex. I haven't made any forms or reports
yet. I want to get everything else working correctly first.

My throw away email address is (e-mail address removed) if you want
to send me your email address that way. I will then give you my real email
address. I just don't want to put that on here.

BTW, let me know if I'm asking too much of you. I don't know you, so I'm
not sure if this is something that is fairly simple for you because you're a
genius or if I'm just being greedy and asking for too much.

Very Greatful,
Jody )

Jody,

I'm glad I was able to help. But, telling me "it quit working" is not
giving me much information to go on (imagine providing only that bit of info
to your mechanic and expecting him to tell you how to fix your car). So, if
you'll provide the structure of the table(s) involved, along with the SQL of
the query, I'll be happy to take a look. If you were also to provide sample
data from the table(s), along with the results you expect the query to
return, that would make things even easier.

As for showing a letter grade in the Totals query, that's easy, since the
function I provided can be used pretty much anywhere you want. It will
accept any decimal value and return a letter grade. So, as long as your
query is calculating the percentage as a decimal (and not multiplying it by
100 as before), you can feed that value to the function and get a letter
grade. It doesn't matter whether you do as I suggested below, and feed the
function a calculation that will result in a percentage, or feed it the
actual decimal number itself.

In other words, let's imagine you have some fields in your query named
TotalPossible (total number of points possible), TotalActual (total number of
actual points), and TotalPercentage, which is a calculated field equal to
TotalActual divided by TotalPossible. You could then add a field to your
query using either of the two methods below, and get the same result:

TotalGrade: fGrade([TotalActual]/[TotalPossible])
TotalGrade: fGrade([TotalPercentage])

-Michael

Michael,

Oh my goodness!! I'm very happy about this. This will make my life easier.
I was beginning to think that this wasn't possible. I really appreciate the
time you took to type this out for me.

I would have replied earlier, but I didn't get a message that there was a
reply on this message board so I wasn't checking. I'm so glad I did.

OK, I have a fairly easy question (I think). I did a query that put the
subjects together (instead of showing each paper's grade), added up both the
possible scores and the actual scores, an showed the total percentage for
each subject, kind of like a report card. For some reason, it quit working
and no matter what I do, I cannot get it to work again. Do you know how to
do this? And if so, would it be easy to put the actual letter grade in the
query also?

Thank you,
Jody )

Hi Jody.

If you are still having trouble with the fomatting, try eliminating the
*100 from the formula that calculates the percentage. Then, set the
Format property of that column to "Percent", and the Decimal Places property
to zero.

Additionally, if you place the following function into a module, you should
be able to add a column to your query like this:
Grade: fGrade([ActualScoreField]/[PossibleScoreField])
which will give you the letter grade.

Public Function fGrade(Percentage As Double) As String
Select Case Percentage
Case Is > 1
fGrade = "A+"
Case 0.97 To 1
fGrade = "A+"
Case 0.93 To 0.97
fGrade = "A"
Case 0.9 To 0.93
fGrade = "A-"
Case 0.87 To 0.9
fGrade = "B+"
Case 0.83 To 0.87
fGrade = "B"
Case 0.8 To 0.83
fGrade = "B-"
Case 0.77 To 0.8
fGrade = "C+"
Case 0.73 To 0.77
fGrade = "C"
Case 0.7 To 0.73
fGrade = "C-"
Case 0.67 To 0.7
fGrade = "D+"
Case 0.63 To 0.67
fGrade = "D"
Case 0.6 To 0.63
fGrade = "D-"
Case Else
fGrade = "F"
End Select
End Function


-Michael



:

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

:

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )



:

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Micheal,

I DID IT!! I worked all evening on this and I feel very accomplished, but
it was with your help that I was able to do it.

I can now see each subject's grades.

I also made up all my forms.

I learned how to make a form popup when the database is open that has
clickable buttons on it to take me to each seperate form.

Tomorrow I will be making reports for attendance and progress.

I'm still having problems with it giving me the wrong letter grade on some
items. For example, my daughter is getting a 97% in Spelling (which to be
exact is 0.965397923875433 and this number somehow falls between the given
percentages in the grading key which in turns gave her an F letter grade.
I'll look at it more tomorrow and see what I can come up with.

Thanks so much for your help!!!!! ) I'm so thrilled!!

Sincerely,
Jody )

Jody,

I'm glad I was able to help. But, telling me "it quit working" is not
giving me much information to go on (imagine providing only that bit of info
to your mechanic and expecting him to tell you how to fix your car). So, if
you'll provide the structure of the table(s) involved, along with the SQL of
the query, I'll be happy to take a look. If you were also to provide sample
data from the table(s), along with the results you expect the query to
return, that would make things even easier.

As for showing a letter grade in the Totals query, that's easy, since the
function I provided can be used pretty much anywhere you want. It will
accept any decimal value and return a letter grade. So, as long as your
query is calculating the percentage as a decimal (and not multiplying it by
100 as before), you can feed that value to the function and get a letter
grade. It doesn't matter whether you do as I suggested below, and feed the
function a calculation that will result in a percentage, or feed it the
actual decimal number itself.

In other words, let's imagine you have some fields in your query named
TotalPossible (total number of points possible), TotalActual (total number of
actual points), and TotalPercentage, which is a calculated field equal to
TotalActual divided by TotalPossible. You could then add a field to your
query using either of the two methods below, and get the same result:

TotalGrade: fGrade([TotalActual]/[TotalPossible])
TotalGrade: fGrade([TotalPercentage])

-Michael

Michael,

Oh my goodness!! I'm very happy about this. This will make my life easier.
I was beginning to think that this wasn't possible. I really appreciate the
time you took to type this out for me.

I would have replied earlier, but I didn't get a message that there was a
reply on this message board so I wasn't checking. I'm so glad I did.

OK, I have a fairly easy question (I think). I did a query that put the
subjects together (instead of showing each paper's grade), added up both the
possible scores and the actual scores, an showed the total percentage for
each subject, kind of like a report card. For some reason, it quit working
and no matter what I do, I cannot get it to work again. Do you know how to
do this? And if so, would it be easy to put the actual letter grade in the
query also?

Thank you,
Jody )

Hi Jody.

If you are still having trouble with the fomatting, try eliminating the
*100 from the formula that calculates the percentage. Then, set the
Format property of that column to "Percent", and the Decimal Places property
to zero.

Additionally, if you place the following function into a module, you should
be able to add a column to your query like this:
Grade: fGrade([ActualScoreField]/[PossibleScoreField])
which will give you the letter grade.

Public Function fGrade(Percentage As Double) As String
Select Case Percentage
Case Is > 1
fGrade = "A+"
Case 0.97 To 1
fGrade = "A+"
Case 0.93 To 0.97
fGrade = "A"
Case 0.9 To 0.93
fGrade = "A-"
Case 0.87 To 0.9
fGrade = "B+"
Case 0.83 To 0.87
fGrade = "B"
Case 0.8 To 0.83
fGrade = "B-"
Case 0.77 To 0.8
fGrade = "C+"
Case 0.73 To 0.77
fGrade = "C"
Case 0.7 To 0.73
fGrade = "C-"
Case 0.67 To 0.7
fGrade = "D+"
Case 0.63 To 0.67
fGrade = "D"
Case 0.6 To 0.63
fGrade = "D-"
Case Else
fGrade = "F"
End Select
End Function


-Michael



:

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

:

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )



:

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )

 

[Guest]

Jody,

I think this is the SQL you are looking for (uses revised fGrade function,
see below):

SELECT [qry-GradeBook].Subject,
Sum([qry-GradeBook].Score) AS Score,
Sum([qry-GradeBook].Possible) AS Possible,
[Score]/[Possible] AS [Percent],
fGrade([Score],[Possible]) AS TotalGrade
FROM [qry-GradeBook]
GROUP BY [qry-GradeBook].Subject;

I would think you'd want to group by a StudentName field first, thus giving
a TotalGrade for each Student for each Subject, instead of just a TotalGrade
for each Subject. But, I'm sure you know your requirements better than I do.



As for the function, in altering the code you seem to have left gaps in the
ranges. How about we rewrite the function to make it a bit easier to tweak
the resulting grades, and to add some other improvements:

Public Function fGrade(Actual As Variant, Possible As Variant) As String
On Error Resume Next

If IsNull(Actual) Or Actual = 0 Then
Exit Function
End If

If IsNull(Possible) Or Possible = 0 Then
Exit Function
End If

Select Case Actual / Possible
Case Is >= 1
fGrade = "A+"
Case Is >= 0.97
fGrade = "A+"
Case Is >= 0.93
fGrade = "A"
Case Is >= 0.9
fGrade = "A-"
Case Is >= 0.87
fGrade = "B+"
Case Is >= 0.83
fGrade = "B"
Case Is >= 0.8
fGrade = "B-"
Case Is >= 0.77
fGrade = "C+"
Case Is >= 0.73
fGrade = "C"
Case Is >= 0.7
fGrade = "C-"
Case Is >= 0.67
fGrade = "D+"
Case Is >= 0.63
fGrade = "D"
Case Is >= 0.6
fGrade = "D-"
Case Else
fGrade = "F"
End Select

End Function


Changes:

- The function now must be passed two parameters, Actual and Possible,
instead of the calculated percentage. So, instead of calling it like this:
Grade: fGrade([ActualScoreField]/[PossibleScoreField])
Call it like this:
Grade: fGrade([ActualScoreField],[PossibleScoreField])
(note the only difference is that the slash indicating division has been
replaced by a comma, which separates the numbers into two different
parameters rather than performing a calculation)

- Percentages in the Select Case statement are now exact values rather than
ranges, and reflect the minimum percentage necessary for the specified letter
grade. In other words, .93 exactly will result in an A, as will .9300001.
However, .9299999 will result in an A-. Feel free to alter the current
percentages as you see fit.

- The parameters are now defined as Variant rather than Double. This means
that the function can now accept null values, and will return a blank
(instead of an Error) if either parameter (or both) is null.

- The On Error statement has been added to avoid returning Errors in case
the function is passed incorrect values (i.e. text instead of numbers).



-Michael

Micheal,

I DID IT!! I worked all evening on this and I feel very accomplished, but
it was with your help that I was able to do it.

I can now see each subject's grades.

I also made up all my forms.

I learned how to make a form popup when the database is open that has
clickable buttons on it to take me to each seperate form.

Tomorrow I will be making reports for attendance and progress.

I'm still having problems with it giving me the wrong letter grade on some
items. For example, my daughter is getting a 97% in Spelling (which to be
exact is 0.965397923875433 and this number somehow falls between the given
percentages in the grading key which in turns gave her an F letter grade.
I'll look at it more tomorrow and see what I can come up with.

Thanks so much for your help!!!!! ) I'm so thrilled!!

Sincerely,
Jody )

Jody,

I'm glad I was able to help. But, telling me "it quit working" is not
giving me much information to go on (imagine providing only that bit of info
to your mechanic and expecting him to tell you how to fix your car). So, if
you'll provide the structure of the table(s) involved, along with the SQL of
the query, I'll be happy to take a look. If you were also to provide sample
data from the table(s), along with the results you expect the query to
return, that would make things even easier.

As for showing a letter grade in the Totals query, that's easy, since the
function I provided can be used pretty much anywhere you want. It will
accept any decimal value and return a letter grade. So, as long as your
query is calculating the percentage as a decimal (and not multiplying it by
100 as before), you can feed that value to the function and get a letter
grade. It doesn't matter whether you do as I suggested below, and feed the
function a calculation that will result in a percentage, or feed it the
actual decimal number itself.

In other words, let's imagine you have some fields in your query named
TotalPossible (total number of points possible), TotalActual (total number of
actual points), and TotalPercentage, which is a calculated field equal to
TotalActual divided by TotalPossible. You could then add a field to your
query using either of the two methods below, and get the same result:

TotalGrade: fGrade([TotalActual]/[TotalPossible])
TotalGrade: fGrade([TotalPercentage])

-Michael

Michael,

Oh my goodness!! I'm very happy about this. This will make my life easier.
I was beginning to think that this wasn't possible. I really appreciate the
time you took to type this out for me.

I would have replied earlier, but I didn't get a message that there was a
reply on this message board so I wasn't checking. I'm so glad I did.

OK, I have a fairly easy question (I think). I did a query that put the
subjects together (instead of showing each paper's grade), added up both the
possible scores and the actual scores, an showed the total percentage for
each subject, kind of like a report card. For some reason, it quit working
and no matter what I do, I cannot get it to work again. Do you know how to
do this? And if so, would it be easy to put the actual letter grade in the
query also?

Thank you,
Jody )




:

Hi Jody.

If you are still having trouble with the fomatting, try eliminating the
*100 from the formula that calculates the percentage. Then, set the
Format property of that column to "Percent", and the Decimal Places property
to zero.

Additionally, if you place the following function into a module, you should
be able to add a column to your query like this:
Grade: fGrade([ActualScoreField]/[PossibleScoreField])
which will give you the letter grade.

Public Function fGrade(Percentage As Double) As String
Select Case Percentage
Case Is > 1
fGrade = "A+"
Case 0.97 To 1
fGrade = "A+"
Case 0.93 To 0.97
fGrade = "A"
Case 0.9 To 0.93
fGrade = "A-"
Case 0.87 To 0.9
fGrade = "B+"
Case 0.83 To 0.87
fGrade = "B"
Case 0.8 To 0.83
fGrade = "B-"
Case 0.77 To 0.8
fGrade = "C+"
Case 0.73 To 0.77
fGrade = "C"
Case 0.7 To 0.73
fGrade = "C-"
Case 0.67 To 0.7
fGrade = "D+"
Case 0.63 To 0.67
fGrade = "D"
Case 0.6 To 0.63
fGrade = "D-"
Case Else
fGrade = "F"
End Select
End Function


-Michael



:

Thanks again Wayne. For some reason it's not setting the decimal points that
way. I've tried over and over. I'll get it figured out since somehow since
you've reassured me that's the way to do it even if it's not working
correctly yet. I'll just try to make another query and see if this fixes it.

Thank you so much for all your time. I really appreciate it!
Jody )

:

Hi

Open he properties of the column on the query to set the decimals.

You could use a nested if to get the grades or create a new table and insert
the grades and percentage - either way it would be pro better to simply type
them in

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

YOU'RE AWESOME!!

Thank you very much.

I have a couple more questions, but I didn't want to overwhelm anyone.

Well, I first have a new question. Do you know how I round the percentages
so there's no decimal points? I know how to do this in a datasheet, but not
a query.

More importantly, would you happen to know how I'd now insert a letter grade
automatically from the percentage grades?

Grade Key would be:

A+ A A- B+ B B-
100-97% 96-93% 92-90% 89-87% 86-83% 82-80%

C+ C D+ D- D C- F
79-77% 76-73% 69-67% 62-60% 66-63% 72-70% 59-0%

Thanks Again!
Jody )



:

Hi

best to do it in a query - create a new column and insert this

Percent([PosibleScoreField]/[ActualScoreField])*100)


--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.



:

Hello, I've made a gradebook for my daughter's homeschooling. On all her
grades, I've put the possible score and her actual score. I would like for
the datasheet to automatically figure out what the percentage is on each
paper. For example, if she scored 10 out of 10 possible, I would like 100%
to show up automatically in the next column without having to figure this out
myself for every paper. Is this possible? Or would it have to be in a
query. Either way, does anyone know how to do it?

Thank you,
Jody )