Price Dependant on Quantity

If I have a table of customers and the prices they are charged for a
product depending on the quantity they purchase (such as);

0-10 Items $1.00
11-20 Items $1.25
21-30 Items $1.35

How can I set up my databse to ensure the correct price is selected VIA
A QUERY when a certain qunatity is entered IN ANOTHER TABLE ?

In essence, I am trying to set a price within a given quantity band,
ie. if the quantity was 9 then the price $1.00 would be selected, the
quantity 23 then the price $1.35 would be selected.

PLEASE CAN SOMEONE GIVE ME SOME GUIDANCE ?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

There is a pretty cool way to do this but it can be a little difficult.
I have done a similar myself. If you have a lot of different products
there can be a lot of data to enter. Here is what you do.

Step 1

Create a new table called 'tblPrice' with Fields: fldProductID,
fldQuantityA, fldQuantityB, fldPrice. Where fldQuantityA is the low
end of the spectrum and fldQuantityB is the high end. Here is an
example for 2 scenerios of one product whose product ID is 1.

(1-10 items = $1)...fldProductID = 1, fldQuantityA = 1, fldQuantityB =
10, fldPrice = 1

(11-20 items $0.50)...fldProductID = 1, fldQuanityA = 11, fldQuantityB
= 20, fldPrice = .50

You will need to create these records for all products. I had a temp
do this for me. I had 5500 items to enter and I had a temp work on it
for 2 hrs a day and she got it done in 3 days. Pretty simple.

Step 2

You will now need to create a query. Include all of the fields from
tblPrice and (I am assuming you have a table that shows how many items
your customer has purchased) include from the purchasing table the
field with the number of items purchased. Call that field fldItems.
Be sure to relate the two tables by fldProductID. In the criteria box
of fldItem in design view key in

=[tblPrice].[fldQuantityA] And =[tblPrice].[fldQuantityB].

This criteria will only pull up records from both tables where the
number purchased falls between fldQuantityA and fldQuantityB thus
showing only the price for that scenerio. Let me know if you have any
questions. There are a few ways to find the total price of all the
items purchased...if you need help with that let me know.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Thanks for the help Shiner ....

I have built the database as per your instructions but it doesn't seem
to be working.

I have attached the databse ...... could you have a look at it for me
?

Many thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

I would be happy to look at it for you. I cant seem to find the
attachment though. can you try to post it again?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

this is a test...I am just checking to see where an attached file shows
up.


+----------------------------------------------------------------+
| Attachment filename: begonia2.jpg |
|Download attachment: http://www.MSAccessForum.com.com/forums/attachment.php?postid=351958|
+----------------------------------------------------------------+

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

This forum does not support .mdb files and it is too big when zipped.

When I run the query it returns no records ..... can I mail it you 'off
line'.... ?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

How big is the zip file? You can email it to me at . I
dont know how large a file MSN will allow me to receive. Showing no
records is most likely a problem with criteria or the relationship
between the tables in the query but I cant think of why it would show
NO records...usually it would show too many records. Try emailing it
to me though.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/