Customer Discount To Product Discount Group (1 Viewer)

Hi I'm hoping someone can help me with this problem (the access problem.. not the other one ! ) I have a table of Products, each item belongs to a product group, I have a table of Customers, amoungst the normal customer fields I have fields named after the item discount groups so that each customer has his own discount for that group of products... when I enter an item into the sales order form I would like the discount for that customer for that item(item group) to go into the discount box.. sounds easy if you say it fast... I hope someone can help me with this !

Would it be 1 discount rate per cleint or would their be differnt discounts according to products

if the first, nice and easy put the discount on the client table
if however it depends upon the product then this is slightly more complicated

we would need to know how many products groups are we talking about
lets take five to begin with you could have 10 extra fields on your customer table (5 times 2) one for productcode and one for discount per product group
then have a lookup on your order table to check all 5 productscodes and get result

so if productcode 1 customer gets 5% off if productcode 2 customer gets 7.5% off
you would need some sort if statement in the background if productcode = 1 then a look up to the corresponding field on the customer table -- If statements driven me a bit loopy - did one laast night very easy - but took an hour to figure it out and get it working so best to ask soemone who can write this in there sleep .

just re-read your threrad
its if staements you require
if productcode = 1 then discount = x (from table )

easly doen - but I will bail out as I mentioned if staements make me a bit loopy

cheers bud... I had an idea that IF statements would come into it.. and they do the same to me ... I feel like I've climbed Everest when I get one to work properly.

Did one last night
if x="blur" then Y=combobox.coloumn(1)
else
it took me 1 hour (It was a lot more fancy than that but the whole if this then do that , else do something else

Ahhh - I should do more of em to get to understand them

g

I think I sense a normalisation problem. When you say

nexus2011 said:

amoungst the normal customer fields I have fields named after the item discount groups so that each customer has his own discount for that group of products...

Click to expand...

Do you mean that you have a separate column in the table for each product group?

Hi, Yes I have seperate columns for each of the 20 product groups so that when I create a new customer I can give then there own discount profile.

Steve

That's not the right way to do it. You need a discount table that links to the customer table and the prodct group table and holds the discount rate relevat to each combination. Thus you will have 20 records instead of 20 columns. This is part of a process called normalisation and is essential to efficient, robust database design. Spreadsheet techniques don't transfer well to relational databases.

That will make your current issue easy to resolve. It will also make life a lot easier when you want to add or delete product groups.

Cheers Neil, I'll make the changes to my tables and see how it goes from there.

Stevie