Getting the correct value from another table

[eagles1]

I have an inventory table and a Price table. The price table stores the date and sell price. The table is join using the Inventory ID.

In the price table, user enter records as follow

Date (dd/mm/yy) Sell Price

01/03/07 $5

15/03/07 $6

01/04/07 $7

15/04/07 $5

They can enter as many records as they wanted. From the inventory table, I like to be able to get the right selling price from the price table , depending on the current date. For example, today I should get $6 as sell price. Then when 01/04/07 comes, the sell price should automatically change to $7. The when 15/04/07 comes, the sell price should change to $5. If I do not enter any more record in the price table, this $5 will take effect forever.

I tried doing this in relationship but somehow I cannot get the correct price.

Any help will be much appreciated

[comment]

Try:

Inventory::cToday ≥ Price::Date

where cToday is an unstored calculation of current date. The relationship needs to be sorted on the Price side by Date, descending. Then place the price field on a layout of inventory.

You haven't mentioned this, but it seems natural that the relationship should also match on a ProductID field.

[eagles1]

I did that but the price field does not return the correct value. It is suppose the return the first value of the price table. But it is return other value down the list randomly. For example I entered the following in the price table

Date Price

31/3/07 $10

30/3/07 $9

29/3/07 $8

28/3/07 $7

The correct value should be $10 (for today 31/3/07). But price field in the inventory layout shows $8. This is not correct. It is random. If I delete 29/3/07, then it is returning $9

[comment]

Computers do NOTHING randomly, so there must be a problem in your implementation.

proof.fp7.zip

[eagles1]

Hi,

Thanks for your "proof ". Now I see what I have done wrong. You are a great help.