Allowing discount against product (1 Viewer)

[Parker]

I have a table of products. Some products are discountable, some are not.

Discount levels are set for each customer account depending on how charitable I feel and the level of trade on each account.

I would like to find a way of automating the application of discounts against product according to the level set for a client.

At the moment we apply the discount manually per product item at the time of order but this is leading to a high level of mistakes at data entry.

Orders are associated with a customer by a join on Customer ID and the discount level is set in the customer table record.

Any ideas about the best way to achieve this?

 

[a.sinatra]

Make a new field in your customer's table with a field to specify how much they get off, the checkout form would have the sub total then how much they get off equals the subtotal?
________
Loratab Rehab Forum

 

[Parker]

Thanks for the reply

This is an obvious answer and I have tried this prior to posting but I can't find a way of getting it to work the way I need.

Problem is if I do this on it's own It will set a general level of discount for the client but it doesn’t take into account that some items can be discounted and some not.

(i.e.) we can discount the charge for the amount of time a Chauffeur spends on a job but we can't discount the amount of fuel he uses, or the amount of money he puts in a parking machine.

So, to clarify, we need to apply discount per product item as well as by client.

The discount level is set for a client and entered into a table that holds details on a client's account. This discount is, initially set manually, according to the size of the account. It is first examined after a 3 month period, then examined every month and automatically modified by a formula that averages the spend over a three month period. So this discount can change

 

[neileg]

Add a field to the product record that holds 1 for discount allowed or 0 for no discount. Multiply the customer discount by this field.

 

[Parker]

Thanks

Add a field to the product record that holds .......

Great, neat and simple.

I think I'll Make that another table though. Otherwise I'll get busted for having a large repeating group again.

Another one bites the dust.

Pete

 

[The_Doc_Man]

If including a "discountable" flag would cause you to violate a "repeating group" issue, your table isn't fully normalized yet.

You should have a product-code table that lists each possible thing that you can charge including a code number to link from your detailed billing records.

Then you should have a many-to-one table that allows accumulation of as many (or as few) charges as required.

The "discountable" flag would go in the product-code table if discountability doesn't depend on the customer but only on the product. No repeating group involved.

Of course, to make this work, you need a customer table and a table of accepted service contracts. You would bill this by creating a series of new entries in a billable-events table that links to the contracts, which links to both the customer and the individual service contract. The billable-events table would link to the product-code table and your discount levels would come through that linkage.

 

[Parker]

Thanks Doc_Man

I don't fully understand anything in Access yet let alone Normalisation. But the way I saw it is this:

tblProduct
ID
ProductDesc
Price

Which looks ok to me? ID is PK. This is linked to order detail by customer and order.

If I then add a column for allow discount I end up with a long col full of 1's and 0's.

I also have to admit that at the time of my reply I hadn't fully thought over the answer and my comments where, partially, a cynical dig 'cause I got my wrists slapped earlier for a table I included to Mile-O that wasn't even in use and had just been formed from a paper form that we use (I felt like a naughty school boy again.

Seriously though, can't praise you guys enough learnt so much just secretly spying on yas and someone is always able to sort out any probs

Still think you should have a credit system though for people to show appreciation

 

[neileg]

We're always happy to help, if people are prepared to listen. You don't have to agree, but do take the answers seriously.

I probably have no more formal training than you and I take a relaxed view to normalisation. However, a 'long column full of 1's and 0's' is not a repeating group. If it is a fact that some items can be discounted and some can't, then you need to record that somewhere, and a field in the product table is the right place to have it.

My knowledge has advanced hugely since I started hanging out here. I've had some slapped wrists, too, but I've also reached the point where I can help people on here and my colleagues and staff at work.