Dear All,

I have a small business selling parts. My main product list has several thousand lines but my customers only ever buy a limited selection up to a maximum 50. My main product database has a Retail Price for each part but my customers each have a discount which is calculated using 6 different criteria (eg Payment method, payment period, Monthly volume etc) and this in turn gives them their net price. Every month I send all my customers an updated quote (based on their regular selection) which takes into account both fluctuations in the underlying Retail Price and any modification to their discount level.

Thanks to Peter Schroeder the above bit works fine leaving me completely free to run into the next problem, which is:

...Still on my customer quotes - apart from the selection which is proper to each customer I also need to make a "default" selection that I can attach or apply to all/some customers; a sort of monthly specials list, sometimes I have 2 or 3 specials at once each comprising a reduced selection of products, none of which feature on the customers individual quotes.

I haven't started to do this seriously but my only attempt was a failure: I can make the selection to a form as I would for the customer requirement but as soon as I attribute it to a customer the now belongs to that particular customer. What I want to do is make a selection of products that I can match to many different customers from another list which is the result of a query. I suppose again that the answer might be in VB because I can't see how to do it in SQL, but.....as on the last one, I don't know what I'm doing.

I have a feeling that this is probably obvious but I can't see it.

As always, all help most gratefully recieved.

DS

I think your difficulty lies in the fact that you're trying to fit this new process into your current one when, to my view, the new one operates quite differently.
Right now you have
....clients who
....select parts.

While the new process is
....I want to offer this special deal (create a specials table)
....On selected parts
....To selected clients.

So I'd design this process from the ground up as a separate entity. Create the special. Select the parts you want to offer on special. Then select some subset of your clients to offer the special to. The selection criteria can be anything you choose, clients who generally order these parts, clients who spend a lot of money with you, clients with a vowel in the third letter of their name, yadda, yadda. After you've assembled the data you can then make use of any/all the functionality of your current process to determine things like discounts, etc.

That's my two cents worth.

Rick

Thanks Rick...it was the answer I was rather hoping not to get but thanks....

I was trying to see if I could avoid creating "specials" tables but I suppose in the end you're quite right. The easiest way is the best.

I'll get on the case soonest and before you know it, I'll be back with the next stupid question. I'm nothing if not predictable.

Actually, I already know what the next question will be: my regular updates are OK and I can update one by one but what I'd really like to do would be to mass update, i.e use a query to select my group of customers and then up date them all by a single click instead of opening their files one by one and updating from there. I got the query already but the automatic bit......

Anyway, thanks for your help - worth a lot more than 2 cents!!!

All the Best,

DS

Without knowing how your processing works it's kinda hard to make informed suggestions. I can't help but see purchase orders in my head so my initial thought is use the specials processing to create a purchase order and add it to the regular purchase order table.
To make it even more general..... You must have a number of steps you go through before you reach your final destination. The "specials" process is substituted for several of the steps but at some point you want to join back into the main flow so go through the process and produce a table of records containing the "specials' stuff in the usual format then just append them to you normal table and proceed as usual.
There you go, you asked for driving directions between NY and LA and I just told you to get in your car and head west. Really helpful I am.
I'm sure some of the other folks will come up with better suggestions for you. I do most of my work using VB code so my solutions will probably be less elegant then theirs but we'll get you through this.

Rick