Database tracking points based rewards system for a mall.

One table keeps rules for calculation how many points are rewarded for a
given purchase. There could be a hundred rules that change from month to
month. They include things like points per dollar spent, rewards that apply
only to one store, upper limits on points per transaction, etc.

What is the best way to define a function that will calculate the correct
number of points based on said rules. I could do my own Calculate_Points
function, but every time I call it it would have to reference the "Rules"
Table.

It would be better to have a fixed function that could be updated on command
or upon opening the program, so that the calculations run faster.

Is there a way to use a sub to create a new function, or should I just
update the query that populates the table recording the points?

On Tue, 7 Oct 2008 11:20:04 -0700, Elizabeth- MDC
<(E-Mail Removed)> wrote:

There is nothing wrong with referencing the Rules table every time, is
there? If you are concerned about performance, don't be until you
prove it is an issue. Then you could open a recordset at Startup time,
and not close it until the app closes. The recordset then becomes a
fancy array that will be VERY fast.
Doing simple math against a hundred or so records should yield
sub-second results.

-Tom.
Microsoft Access MVP




>Database tracking points based rewards system for a mall.
>
>One table keeps rules for calculation how many points are rewarded for a
>given purchase. There could be a hundred rules that change from month to
>month. They include things like points per dollar spent, rewards that apply
>only to one store, upper limits on points per transaction, etc.
>
>What is the best way to define a function that will calculate the correct
>number of points based on said rules. I could do my own Calculate_Points
>function, but every time I call it it would have to reference the "Rules"
>Table.
>
>It would be better to have a fixed function that could be updated on command
>or upon opening the program, so that the calculations run faster.
>
>Is there a way to use a sub to create a new function, or should I just
>update the query that populates the table recording the points?