Formula help

[bond]

I am trying to figure out a formula for the following
situation.

Each month our employee may or may not recieve safety
points for being safe. After accumalating points, they
can spend it on company Hats, shirts .... my problem is
that their point total can not be grater than 300, so if
they don't use it they loose it.

Right now I have a querry totaling all the monthly points
and another querry totaling all the monthly points spent,
and a third queery using the two above querry's in a "IIF"
statement that if the total points accumalated - total
points spent is greater than 300 than only show 300. The
problem is that if they are above 300 they are not
actually losing that months points.

Example: 350 accumalted points, only can have max 300.
(the formula temporarely cuts it back to 300) spent 25
points. Instead of being 275, which would be
representative of losing 50 points, it will still show 300
and actually be 325 points overall that can be spent over
time.

Any help?

Craig

 

[Paul J. Sweeney]

Craig:

I'm not sure of what kind of user interface you have (if
any), or if you are running queries directly from
the "Queries" tab in the main database window. If you
are doing the latter, you could create an "Update Query"
that would use a conditional in your "points" field to
return only records > 300, and then tell it to change
those back to 300. In effect, lopping off the amount >
300.

You create an update query by simply selecting "Update
Query" from the "Query" option on the toolbar while in
the Query Design Grid. Create a new query in design
view, add your table(s), select "Query" from the toolbar,
then "Update Query". You will see the grid change,
adding an "Update To" row. Put the conditional ("> 300")
in the "Criteria" row of your "points" field, and
put "300" in the "Update To" row of the same "points"
field.

If you have a user interface designed with a form, you
could use VBA code to accomplish the same thing, or you
could call the update query from the form via a command
button.

Hope this is helpful!

PJS