Calculations in query mode

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am developing a DB for working out for myself and my
fiancee. When we lift weights and we do different set
numbers and try to add the total weights, if we do not
fill in all the sets then that amount is zero. for
example if we are doing the bench press and we only do 3
sets instead of 5 when I do the calculations of adding
each set which I do 5 sets then the number is zero instead
of the appropriate number why. The formula that I used is
the following Total Weight:[wt1]*[rep1]+[wt2]*[[rep2]+[wt3]
*[rep3]+[wt4]*[rep4] and so on, if I only do 3 the answer
is zero that is returned. How can I do this correctly so
that if I do 1 set or rep or 10 I can get the right answer?
 
I am developing a DB for working out for myself and my
fiancee. When we lift weights and we do different set
numbers and try to add the total weights, if we do not
fill in all the sets then that amount is zero. for
example if we are doing the bench press and we only do 3
sets instead of 5 when I do the calculations of adding
each set which I do 5 sets then the number is zero instead
of the appropriate number why. The formula that I used is
the following Total Weight:[wt1]*[rep1]+[wt2]*[[rep2]+[wt3]
*[rep3]+[wt4]*[rep4] and so on, if I only do 3 the answer
is zero that is returned. How can I do this correctly so
that if I do 1 set or rep or 10 I can get the right answer?
Click to expand...

Well, one way would be to restructure your database so that you don't
have repeated fields. Rather than *fields* for wt1, wt2, wt3 a better
design would be to have separate *records*.

That said, if any of the fields is NULL (blank), the entire expression
will be NULL. Use the NZ() function to convert NULL to zero:

NZ([wt1])*NZ([rep1]) + NZ([wt2])*NZ([rep2]) + ...

or, of course... complete your sets... <g, d & r>
 
Back
Top