And another table is recipe and items had field UnitCode when we buy items
we input quantities as 1 and unitcode is kg, then when sales food the recipe
use items smaller than,for example quantities 100 and unitcode is "g". How
could i know the cost of recipe which i sold. I tried to code by use VBA but
it still not work. Any pointer would be very appreciate.

Thank you in advance.
Suttipong

This can be done more efficiently by using joins in queries than by using
lookups in code.  Since you haven't described the structures of your other
tables, I'll call the one you've described "tblConversions", and I'll assume
that the other two tables of primary concern are similar to the following:

tblProductPrices
------------------------
ProductID
UnitCode
UnitPrice

tblRecipeIngredients
-----------------------------
RecipeID
ProductID
Quantity
UnitCode

To extract the cost of each ingredient in each recipe, you could use the
following query:

SELECT tblRecipeIngredients.RecipeID, tblRecipeIngredients.ProductID,
tblRecipeIngredients.Quantity, tblProductPrices.UnitPrice *
tblRecipeIngredients.Quantity / tblConversions.Factor AS TotalIngredientCost
FROM (tblProductPrices INNER JOIN tblRecipeIngredients ON
tblProductPrices.ProductID = tblRecipeIngredients.ProductID)
    INNER JOIN tblConversions ON (tblConversions.UnitCode =
tblProductPrices.UnitCode) AND (tblRecipeIngredients.UnitCode =
tblConversions.ComposedOfUnits);

For this to work properly, you must have a record in tblConversions with
every possible ComposedOfUnit.  For example, even if prices by mass are
always for a kilogram, and some recipes use kg as the unit in
tblRecipeIngredients, there must be a record in tblConversions with UnitCode
= "kg", ComposedOfUnits = "kg", and Factor = 1.

HTH,
Nicole