macro for increasing table values

[damji]

Hi, I'm creating a database for my students at school, and i need some help.

We have a list of students, all their information, and the grade that they are currently in. There is a field(called: Grade) in a table (called: students), and at the end of the year, i need a macro that increases their grade by 1, how do i do that?

any help is much appreciated

Thanks

 

[Mike375]

The SetValue action will do that but a better way is to use an Update query.

 

[USnick]

Say your table is as follows:

NAME GRADE
Johnny 4
Sally 3
Joe 3

Create an update query that updates GRADE to GRADE+1. The SQL statement is:
UPDATE Table1 SET Table1.GRADE = [GRADE]+1;

You can build it in design mode really easy. I attached a sample db with just the table and query to make it easier to see and try.

Now the more difficult part is the logic for when and how you invoke the query...but that wasn't your immediate question.

 

[damji]

follow-up

Thanks, that worked like a charm.

I have a follow-up question related to that, I've started to input the students, and have run into another problem.

there are students who are in junior kindergarten and senior kindergarten, their grades are labeled as jk and sk.

right now i have a query for adding 1 to their grade at the end of the year, but what i would like to do is add a statement as follows: (this is how it would look in java)

if (grade = sk)
{
grade = 1;
}
else if (grade = jk)
{
grade = sk;
}
else
{
grade = grade + 1;
}

i've tried to do this on my own in access, but i cant figure it out

Thanks

 

[Mike375]

Check here for IIF

http://www.webcheatsheet.com/SQL/access_functions/iif.php

And on the index on the left go to Switch function.

IIF is the common one used, at least on this forum. In its basic form it gives a result for a true or false.

IIF([FieldName] >10 (orwhatever),could be a fieldbame or text as in "grade" or a number etc,and another anser here)

The IFF functions can be nested. An easy way to play about and get a feel for nested IIFs is to make one calculated field in a quert an IIF function and then create another calculated field that is an IFF function and one of the answers or "the test" is the previous calculated field.

To make a calculated field in a query you open the query in design view and on the field line type the field name:

YourNewFieldName:Function or whatever

 

[USnick]

one point though... you are mixing alpha and numeric values in this nested iif, which leads me to think you are doing the same in your tables/queries.

I do this sometimes as it is effective for certain data structures, but in order to increment it you need to use a conversion function like VAL() and you need to handle the error when the initial value is SK

Good luck