Add discount

[Neo]

I have a table of customers. In the customers table there are details like, Name, Address, Telephone and discount.

What I want to do is when a new customer is added their discount is 0 every 5 times they place an order their discount increases by 1 (which means 1 percent)

and ideas

thanks

 

[Fizzio]

There are 2 ways you could do this. The easiest probably by an update query.

Use the tables tbl_Orders, qry_CustomersOrders (or your equivalent)

Essentially you need to count the number of orders a particular customer has made in the qry_customersorders

In the update_to box, set this to something like

CInt([Discount] * ([CountofCustomerOrders]/5))

where CountofCustomerOrders would be taken from a select query simply counting the number of orders each customer has made.

The downfall of this is that when the customer makes 500 orders, they will get everything for nothing as the discount will be 100 and above that, you will be paying them to order stuff as their discount will be over 100!

Get around this by setting a maximum on the count of orders that you want to pull out eg 100 orders as this will give a maximum discount of 20.

The other way is via VB using Dcount and an updateable recordset but The update query method should be easier.

hth

 

[Fizzio]

Good points Pat, thanks for the correction. Did not know about the \ and / differentiation.
Shows up my inexperience with handling money