Product Price dependant quantity

[skal via AccessMonster.com]

Hi,

I am creating a database for quotation purposes.
The functionality of the database is to input part number and quantity and
get the price of the part dependant on the quantity entered.

E.g. 1-4 £12.00
5-9 £10.00
10+ £8


At the moment I have one table with part No,description etc and in a another
table partno and prices dependant on quantity. E.g

PartNo MinPrice maxPrice
P1 1 4
P1 5 9

My problem is how do I create the input table where I can enter the part no
and quantity and get the price.
I would Appreciate any help

thanks

 

[Allen Browne]

Presumably you already have a Product table, and now you are building the
ProductPrice table, where one product can have multiple prices. The fields
will be:
ProductID foreign key to Product.ProductID
MinQty number the minimum number to get this price.
PriceEach currency the charge each for this quantity of this
product.
Example data:
ProductID MinQty PriceEach
1 1 $12.00
1 5 $10.00
1 10 $8.00
2 1 $25.00

Then, when someone orders a quantity of this product, you can get the price
with this function:
http://allenbrowne.com/ser-42.html
like this:
ELookup("PriceEach", "ProductPrice", "(ProductID = " & Nz([ProductID],0)
& ") AND [MinQty] <= " & Nz([Qty],0) & ")", "MinQty DESC")

 

[skal via AccessMonster.com]

Presumably you already have a Product table, and now you are building the
ProductPrice table, where one product can have multiple prices. The fields
will be:
ProductID foreign key to Product.ProductID
MinQty number the minimum number to get this price.
PriceEach currency the charge each for this quantity of this
product.
Example data:
ProductID MinQty PriceEach
1 1 $12.00
1 5 $10.00
1 10 $8.00
2 1 $25.00

Then, when someone orders a quantity of this product, you can get the price
with this function:
http://allenbrowne.com/ser-42.html
like this:
ELookup("PriceEach", "ProductPrice", "(ProductID = " & Nz([ProductID],0)
& ") AND [MinQty] <= " & Nz([Qty],0) & ")", "MinQty DESC")

I am creating a database for quotation purposes.
The functionality of the database is to input part number and quantity and

[quoted text clipped - 18 lines]

thanks

Hi Allen

Thank you for your message, I have understood the first part of you message
and have created the table with the product prices as you have suggested but
I do not know how to use the elookup. Please could you explain in more detail
where the code should be placed?

Many thanks

 

[skal via AccessMonster.com]

Hi Allen

Thank you for your message, I have understood the first part of you message
and have created the table with the product prices as you have suggested but
I do not know how to use the elookup. Please could you explain in more detail
where the code should be placed?

Many thanks

 

[Allen Browne]

Choose the Modules tab in the Database window.
Click the New button. Access opens a new module.
Paste the code in there.
Save with a name such as Module1.
Close.

You can now use the ELookup() function anywhere in your app. For example,
you might use it in the AfterUpdate event procedure of the combo where the
user chooses the product in the order, as well as the Quantity. You can see
an example of that if you look in the Northwind sample database, the Order
Details subform, and the ProductID combo. (They use DLookup(), but you need
the more powerful ELookup().)

 

[skal via AccessMonster.com]

Hi Allen

Thank you so much for your efforts to help me so far but I am still unable to
fix the problem. Is it possible to e-mail you the database and you could have
a look at it .

Thanks skal

 

[Allen Browne]

Thanks for asking first, but we can't really open everyone's databases and
work through the unending list of things that come as a result.

Are you getting an error message? no result? ...?