user18951

Lookup Failing

Discussion created by user18951 on Feb 13, 2013
Latest reply on Feb 15, 2013 by user18951

Probably a basic question, but:

 

Table Quotes:

 

pkQuoteID

fkConstructionID

 

Table Construction:

 

pkConstructionID

Description

 

Table UnitPrice:

 

pkUnitPriceID

fkConstructionID

Quantity

PricePerThousand

 

Table QuoteQuantityPrice:

 

fkQuoteID

Quantity

UnitPrice

Extended Price

blah

blah

 

Each Quote has one construction and can have multiple prices.

The problem is this pricing was set up on a matrix. In the UnitPrice table, there may be a quantity for 500, 1000, and 2500. Each with a seperate per thousand price.

I need to get the unit price into the QuoteQuantityPrice table, but the amount quoted might be 1250 or 2000, and I need to lookup the price for the lower quantity.

 

I've tried lookups, but I'm not getting anywhere.

 

Any advice would be appreciated.

Outcomes