Autoform

Question

Hi,

I have a question, which I am sure is pretty straight forward to answer but not been able to work it out yet.

I have a database and currently struggling to create a form for it which does the following:

When inputting a new record, automatically puts a Y into the Interim field if the ID is 1+4+4, etc and allocates a group of numbers (these numbers follow each other in a sequence) to the Interim Units field. Every other record that doesn't meet the above criteria is then marked as N in Interim but marked as Y in Summative and gets the same numbers as Interim in a sequence.

So something like this:

ID, name, ..., Interim, Interim Units, Summative, Summative Units

1,  aaa, ...,   Y,       1,2,5,         N              

2,  bbb, ...,   N,            ,         Y,          3,4,6

3,  ccc, ...,   N,            ,         Y,          1,2,6

4,  ddd, ...,   N,            ,         Y,          3,4,5

5,  eee, ...,   Y,       1,3,6,         N, 

6,  fff, ...,   N,            ,         Y,          2,4,5

7,  ggg, ...,   N,            ,         Y,          1,3,5

8,  hhh, ...,   N,            ,         Y,          2,4,6

9,  iii, ...,   Y,            ,         N,

etc...

Can I get some help, please?

Answer 1

Thanks for your reply. I shall give it a go.

Answer 2

Well, as a general rule we don't store calculated values. So if the value of Interim is based on a business rule, it should be a Calculation. If I'm understanding the need, any record that is evenly divisible by 4 (after subtracting 1) should have Interim as Y. So I would use something like this:

IIF(ID = 1 OR (ID-1) MOD 4 = 0, "Y","N")