Product Discounts for Customers

[KenshiroUK]

Hi I am fairly new to Access and have been struggling all weekend to try and accomplish this task. I have been asked to create an orders database for purchase orders on certain products which is fine. However I have now been told that each customer will have they're own many discounts on many products. So it's a many-to-many relationship.

Product 1 - Customer A = 10% Discount
Product 1 - Customer B = 5% Discount
Product 1 - Customer C = 20% Discount

Product 2 - Customer A = 5% Discount
Product 2 - Customer B = 10% Discount
Product 2 - Customer C = 3% Discount

So it would be something like this. How I cannot seem to get this work at all, and am struggling on this last bit. In my form a have combo boxes, so when a order is placed for that particular customer for that particular product, the discount is automatically applied and stored into my Orders table using a query or some sorts. I am really really struggling on this I would appreciate any help what so ever.

 

[bob fitz]

Sounds to me as though you will need a table to store the CustomerID, the ProductID and the the discount to be given.

 

[KenshiroUK]

Hi Bob, I did try that but it didn't seem to work. Not sure what I did wrong either.

 

[bob fitz]

How did it not work.

 

[KenshiroUK]

When I linked the tables up it did not show anything within the select query. When I played around with it basically it came back with all the discounts for that product on Customer ID. If I posted a copy of my database, you couldn't take a look could at it could you?

 

[bob fitz]

I'll try.

 

[KenshiroUK]

I have posted a copy for you.

 

[bob fitz]

KenshiroUK
OK I have downloaded the db. Looks like a solution should be quite straight-forward. Should be able to post back a proposed solution later today when I have a little time to deal with it.

 

[KenshiroUK]

Cheers pal! I appreciate it.

 

[bob fitz]

KenshiroUK
Take a look at the attached db.
It shows how to retrieve the discount for a particular customer for a product.
I don't know if it is of use to you, but I have also added some code in the After Update event of the Quantity text box. If the number entered is > the number in QuantityAvailable field of the products table it changes the entry to = QuantityAvailable and calculates the balance which it puts in Quantity To Follow.
BTW, QuantityAvailable is a calculation and should not be stored in the table. It should be calculated whenever and wherever it is needed.
Let me know if you have any questions about the alterations.

 

[KenshiroUK]

Thank you for doing this. However when I select the second product with Cus 2. The discount doesn't seem to be updating, its just remains the same as the first discount.

 

[bob fitz]

Yes, I see what you mean. I will need to take another look at it. Sorry.

 

[bob fitz]

Hopefully this will be better.

 

[KenshiroUK]

Thank you for doing this my its complete! Could you tell me what you did in order for it to work just in case for future reference. And thank you once again, as for the debit of stock, we do that because we also have a couple of websites to update with that quantity as well you see.

 

[KenshiroUK]

Hi sorry to bother you again. My manager now just asked me even though we have individual discounts on individual products for each customer. He now wants to know if you can apply a bulk discount on items, as well individual items. So Customer 1 gets 10% on Product A, however if Customer 1 orders 30 of A they would get 50% off etc.

Thanks.

 

[bob fitz]

Maybe something like this:

 

[KenshiroUK]

Thank you so much for this! It works perfectly just how I want it. I really do appreciate this.

 

[bob fitz]

Thank you so much for this! It works perfectly just how I want it. I really do appreciate this.

Good. I'm glad it works for you.
In the original db that you posted there seemed to be a number of macros that ran in the After Update event of the Products combo box which I replaced with an Event Procedure. I do not usually use macros and I have no idea what they did. If you need help with getting them to run now, let me know.
I would still advise against storing the QtyInStock in a table.
Also, shouldn't this be altered after each order item is entered, otherwise it is incorrect for the next order of that product?

 

[KenshiroUK]

I believe so yes. But if I wish to update the quantites on a website. how would that work in terms of not storing a the available quantity?

 

[bob fitz]

I believe so yes. But if I wish to update the quantites on a website. how would that work in terms of not storing a the available quantity?

I don't know. How do you do that now?