barry_c Posted October 31, 2004 Share Posted October 31, 2004 Hello all; I have two tables in a database, ORDERS and CLIENTS. Each record in the ORDERS table is unique and specific to one of 18 publications in which our clients buy advertising space. Each client is entitled to a different frequency discount level which may be different in each publication depending on how many ads the client promises to buy in a given 12-month period. Now for example, when I am creating the order for client A in publication 1, I would like to have the correct discount rate automatically entered. I created a third table called DISCOUNTS with the fields PUB_NAME; CLIENT_NAME; and DISCOUNT_LEVEL. In the "Discount" field on ORDERS I used the following calculation: Case (not (IsEmpty(Publication) or IsEmpty(Client)); DISCOUNTS::DISCOUNT_LEVEL) This works but is not 100% satisfactory for the following reasons: 1. It's a lot of extra work to keep up with the new DISCOUNTS table. If a client buys all 18 pubs that's 18 more records to deal with. I'd like to just be able to put the info (publication name, discount level) in the CLIENTS table, and feed that to the ORDERS table. Any alternatives out there? 2. The calculated "Discounts" field on ORDERS is not modifiable after the fact. On rare occasions a frequency discount is not applicable to a certain order, so it would be nice to have some override capability. Thanks for any suggestions you may have. best regards barry Link to comment Share on other sites More sharing options...
This topic is 6786 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!Register a new account
Already have an account? Sign in here.Sign In Now