Hi,
I've read everywhere while searching for solutions that the Golden rule of access is to calculate anything which can be calculated rather that storing it. However, for various reasons the data does need to be stored.
So here's what I'm after. The database is for a primary school, and stores each child's teacher assessment (in the form of a whole number) over a variety of subjects. Assessments are made 4 times a year. So, for example, for reading we have SeptAssess, JanAssess, AprilAssess, and JulyAssess. These are all in a single table. Previously, using excel, the PointsProgress field was populated with the following formula;
=IF(COUNT(N9)=1,(N9-H9),IF(COUNT(L9)=1,(L9-H9),IF(COUNT(J9)=1,(J9-H9),"")))
So, if there was data in the July field, then it would return the July assessment minus the April assessment. If July had no data in it, then it would look in the April field, and so on.
I would like the field to be populated by a similar formula, but can't work out how this would be done. I even thought of having hidden text boxes which would work out the equation and then copy it into the PointsProgress field - however, I haven't a clue how to do it! Could this be done using a query? By the looks of other queries I thought it might be the closest to the solution - hence why it's in the query section - my apologies if it would be better placed somewhere else...
Thanks in advance.
I've read everywhere while searching for solutions that the Golden rule of access is to calculate anything which can be calculated rather that storing it. However, for various reasons the data does need to be stored.
So here's what I'm after. The database is for a primary school, and stores each child's teacher assessment (in the form of a whole number) over a variety of subjects. Assessments are made 4 times a year. So, for example, for reading we have SeptAssess, JanAssess, AprilAssess, and JulyAssess. These are all in a single table. Previously, using excel, the PointsProgress field was populated with the following formula;
=IF(COUNT(N9)=1,(N9-H9),IF(COUNT(L9)=1,(L9-H9),IF(COUNT(J9)=1,(J9-H9),"")))
So, if there was data in the July field, then it would return the July assessment minus the April assessment. If July had no data in it, then it would look in the April field, and so on.
I would like the field to be populated by a similar formula, but can't work out how this would be done. I even thought of having hidden text boxes which would work out the equation and then copy it into the PointsProgress field - however, I haven't a clue how to do it! Could this be done using a query? By the looks of other queries I thought it might be the closest to the solution - hence why it's in the query section - my apologies if it would be better placed somewhere else...
Thanks in advance.
