Product Price dependant quantity

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

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")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"skal via AccessMonster.com" u14847@uwe wrote in message
news:55c5933f3c922@uwe...

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 wrote:
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

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

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().)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"skal via AccessMonster.com" u14847@uwe wrote in message
news:55c703e481ea2@uwe...
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

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200510/1

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? ...?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"skal via AccessMonster.com" u14847@uwe wrote in message
news:5611c931c2ec4@uwe...

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 .