How do you derive an amount available in a query?

[Guest]

Hello everyone.

I have a table that records Emp_ID, date, and sale amount. For every $ of
sales = 1 point. Points may be redeemed for a promotional products order. I
have an orders table, and an order detail table with line numbers,
product_id's, quantity, and Required_Points.

During the design, I thought the proper way was to total accumulated points,
subtract past redeemed points (from orders), to get the current available
points.
How do I make the query, or is it just a calculated field, that I can use on
my orders form that readily shows available points for ordering?

Thank you very much in advance!

 

[Michel Walsh]

Hi,



Something like

DSum("Amount", "Sales", "ClientID=" & ClientID ) * 0.04 - DSum(""
RedeemPoints", "Orders", "ClientID=" & ClientID )


if you have two different table (or one table), and clientID a numerical
value. I assume a 4% conversion $ to Points, for illustration. You can also
make the computation in a query, replacing the DSum with a (SELECT SUM(..)
.... ) as appropriate (faster for execution, but may take a little bit
longer to open the form).


Hoping it may help,
Vanderghast, Access MVP