Select Price with quantity > 400 and < 600 2

[LNBruno]

Pre-weekending seems to have had an adverse effect on my mental faculties! I've got a table that contains quantities and prices:

QTY PRICE
400 12.00
600 10.00
1000 9.00
1500 8.00
2500 7.50
5000 7.00

I need to do is write a query that will get the price for a user-specified quantity that's within a range, something like this:

SELECT (price * 500) AS EstCost
FROM tablename
WHERE quantity...

And that's where I lose it. 500 is > 400, but < 600, so what I need to do is get the 12.00 as my price, but I'm totally blocked at how to pull it off.

I know I'm going to kick myself when someone posts the answer!

< M!ke >

 

[AWithers]

Unless I'm missing something

where qty >=400 and <600

Andy

 

[LNBruno]

That works, but I need it to be a bit more dynamic than that. User changes the input value to 1750; then what?

< M!ke >

 

[AWithers]

Then its a little more complicated

SELECT DISTINCT (SELECT Max(tablename.price) AS MaxOfprice
FROM tablename
WHERE tablename.qty<=[quantity])*[quantity] AS EstCost
FROM tablename;

 

[LNBruno]

Dear Sir or Madam:

THANKS!!!!

Here's the finished product:

SELECT DISTINCT (SELECT Max(tblPriceMatrix.price) AS MaxOfprice
FROM tblPriceMatrix
WHERE ProductID = 1
AND tblPriceMatrix.quantity<=500)*500 AS EstCost
FROM tblPriceMatrix;

< M!ke >

 

[Golom]

So you want to select the record where the quantity is the maximum quantity from the table that is less than or equal to the quantity specified by the user. Is that correct? If so

[tt]
Select ([Enter the Quantity] * Price) As EstCost

From tbl

Where Quantity =
(Select NZ(MAX(Quantity)) From tbl
WHERE Quantity <= [Enter the Quantity])
[/tt]

 

[LNBruno]

Fewer keystrokes! I like it Golom!

SELECT (500 * Price) As EstCost
FROM tblPriceMatrix
WHERE Quantity = 
    (SELECT MAX(Quantity) From tblPriceMatrix
    WHERE  ProductID = 1 
    AND Quantity <= 500)

< M!ke >