Product Multie Pricing

[Dreamweaver]

Hi
I've been trying to get my head around this and after coming up with no search results Gonna ask for ya help.

I've attached a jpg which uses sample data so nobody needs worry as the program displayed will still be available free.

What I'm trying to do is:-

Should a user build programs for general sale they can then sell the first copy at full price but should a customer wish to install other copies then the user will have the option of selling additional copies at decreasing rates depending on how many copies a client purchases\Has so saying that looking at the jpg if I was to bye a program for a network of 20 systems and want to install legal copies on each I would pay the full price for copy 1 and £75 For copies 2-20

My thinking is I first check for any multiie prices for a product when the productID is selected then if that returns >0 then I need to check the invoice system to see if a client has already payed for X copies that way I can get the correct price for the Estimate\Invoice.

The Problem I'm having is I can't seem to get my head around the getting the correct record as it uses a low and high number with a listed price Please see Jpg I have been thinking of using Between Low and High of Low >= X And High<=Y But that's bothering me as I'm not sure if it would just return one record.
If returned more than one record maybe I could use sorting to get me the lowest price for X & Y but I don't feel happy with that?.

Hope that makes sense

Many Thanks

Mick

 

[cocoonfx]

hELLO

I have create a simular database. What i did was build one query and then had and if statement which said:

Net Amount:iif([Total No Items]>5,[Total no Items]*[Price 2],[Total no Items]*[Price 1])

The above is a condensed version of the iif statment has mine had upto 10 different individual prices.

If you have more than 8 say price ranges swap the iif to Switch function where you can do more calculations.

I hope this helps. My database will be going to market hopefully in couple of weeks )

 

[Dreamweaver]

I have create a simular database. What i did was build one query and then had and if statement which said:

Net Amount:iif([Total No Items]>5,[Total no Items]*[Price 2],[Total no Items]*[Price 1])

The above is a condensed version of the iif statment has mine had upto 10 different individual prices.

If you have more than 8 say price ranges swap the iif to Switch function where you can do more calculations.

I hope this helps. My database will be going to market hopefully in couple of weeks )

Thanks cocoonfx I'll look into it.

I have tried querys but can't jet a lock on one record but there's more than one way of skinning a cat so to speek

I now thinking Recordset I should be able to use a recordset Navigation first, Prev, next and compaire whats above and below so I can find the correct record with the price, Thats just a thought at the min but think I could get it working that way???

Many thanks for your help I'll see if I can get the iif working first.

mick

 

[The_Doc_Man]

Think about a discount rate table that looks like this:

tblDiscount
LowQ, long, lowest amount to which this discount rate applies
HiQ, long, highest amount to which this discount rate applies
QDiscount, single, discount amount

Consider this SQL:

SELECT IName, IQuantity, IBaseCost, QDiscount
FROM tblInvoice, tblDiscounts
WHERE IQuantity BETWEEN tblDiscounts.LowQ AND tblDiscounts.HiQ;

You can further complicate matters by having different product type codes and different discount rates based on item type, but this is the basic concept. You are doing a sort of a JOIN except that it is not an "=" join, it is a BETWEEN ... AND ... type of JOIN.

 

[Dreamweaver]

Think about a discount rate table that looks like this:

tblDiscount
LowQ, long, lowest amount to which this discount rate applies
HiQ, long, highest amount to which this discount rate applies
QDiscount, single, discount amount

Consider this SQL:

SELECT IName, IQuantity, IBaseCost, QDiscount
FROM tblInvoice, tblDiscounts
WHERE IQuantity BETWEEN tblDiscounts.LowQ AND tblDiscounts.HiQ;

You can further complicate matters by having different product type codes and different discount rates based on item type, but this is the basic concept. You are doing a sort of a JOIN except that it is not an "=" join, it is a BETWEEN ... AND ... type of JOIN.

thanks The_Doc_Man,

I'll give that a go when I get back from work.

Best wishes

mick

 

[Dreamweaver]

Think about a discount rate table that looks like this:

tblDiscount
LowQ, long, lowest amount to which this discount rate applies
HiQ, long, highest amount to which this discount rate applies
QDiscount, single, discount amount

Consider this SQL:

SELECT IName, IQuantity, IBaseCost, QDiscount
FROM tblInvoice, tblDiscounts
WHERE IQuantity BETWEEN tblDiscounts.LowQ AND tblDiscounts.HiQ;

You can further complicate matters by having different product type codes and different discount rates based on item type, but this is the basic concept. You are doing a sort of a JOIN except that it is not an "=" join, it is a BETWEEN ... AND ... type of JOIN.

Hi The_Doc_Man,
Walking home from work today it came to me why I had a problem with what I wanted to do.

As it goes I was trying very hard to make something simple very complex in that if I wanted to sell software at a reduced price for X number of copies why was I trying to put myself and the users though setting up want I had come up with when All I really needed to do was add another product with same details Plus an indecation it was a multi price deal at X Amount.

The example you gave me will come in very handy In the future I can see how it works just It looks to me to be a sort of lookup function as it deals with stored values in the tables would I be correct there?

Many Thanks

Mick

 

[The_Doc_Man]

It looks to me to be a sort of lookup function as it deals with stored values in the tables would I be correct there?

Absolutely. It just differs in the small detail that it is a bounded lookup rather than an exact lookup. And the discount range table holds the bounds.

 

[Dreamweaver]

Absolutely. It just differs in the small detail that it is a bounded lookup rather than an exact lookup. And the discount range table holds the bounds.

Thanks Mate

Mick