Price Dependant on Quantity

J

jagstirling

If I have a table of customers and the prices they are charged for
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 VI
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, th
quantity 23 then the price $1.35 would be selected.

PLEASE CAN SOMEONE GIVE ME SOME GUIDANCE
 
S

Shiner452

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 product
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 lo
end of the spectrum and fldQuantityB is the high end. Here is a
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, fldQuantity
= 20, fldPrice = .50

You will need to create these records for all products. I had a tem
do this for me. I had 5500 items to enter and I had a temp work on i
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 fro
tblPrice and (I am assuming you have a table that shows how many item
your customer has purchased) include from the purchasing table th
field with the number of items purchased. Call that field fldItems.
Be sure to relate the two tables by fldProductID. In the criteria bo
of fldItem in design view key in

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

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

jagstirling

Thanks for the help Shiner ....

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

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

Many thanks
 
S

Shiner452

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

jagstirling

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 'of
line'....
 
S

Shiner452

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