I have two tables of similar (but not identical) headings and different data 
in each table similar to below. Prices change each day and are listed in the 
prices tables

TblPrices
Date                      A         B        C
24/02/2010           2.66     3.14    1.32
25/02/2010           2.72     3.21    1.39
26/02/2010           2.62     3.14    1.30

TblCurrentPrice
Product           Current Price
    A                    2.62
    B                    3.14
    C                    1.30

I put in a new price everyday in the TblPrice and I would like to make a 
query to update the TblCurrentPrice with the most recent price, so I can 
then use the data from there, elsewhere in the database but I cannot figure 
out how to make a query to update the TBLCurrentPrice from TblPrice as there 
is no common link. Can anybody please explain how this can be done. My Sql is 
extremely limited, so step by step would be really appreciated. If it were in 
excel I simply use 
vlookup with a cell to reference the most recent date but I don't know how or 
if this can be done in Access.

I can make a table of the current prices with the same headings as the 
TblPrices but ths is not what I need as I need to reference current price A 
(or B or C) in another part of the database.
-- 
Very Grateful for all and any help.
Steve

You approach this wrongly.
You don't need a 'current price' table when you already have the data in 
your tblPrices table.
You need a query that extracts the latest price from the tblPrices table and 
you should use that.
Your tblPrices table is also poorly designed, you need a serapare row for 
each price/date/part. Do not combine prices into one row. When properly 
designed, it becomes easy to find the latest price.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they 
eat for a lifetime".


"Steve" wrote:

> I have two tables of similar (but not identical) headings and different data 
> in each table similar to below. Prices change each day and are listed in the 
> prices tables
> 
> TblPrices
> Date                      A         B        C
> 24/02/2010           2.66     3.14    1.32
> 25/02/2010           2.72     3.21    1.39
> 26/02/2010           2.62     3.14    1.30
> 
> TblCurrentPrice
> Product           Current Price
>     A                    2.62
>     B                    3.14
>     C                    1.30
> 
> I put in a new price everyday in the TblPrice and I would like to make a 
> query to update the TblCurrentPrice with the most recent price, so I can 
> then use the data from there, elsewhere in the database but I cannot figure 
> out how to make a query to update the TBLCurrentPrice from TblPrice as there 
> is no common link. Can anybody please explain how this can be done. My Sql is 
> extremely limited, so step by step would be really appreciated. If it were in 
> excel I simply use 
> vlookup with a cell to reference the most recent date but I don't know how or 
> if this can be done in Access.
> 
> I can make a table of the current prices with the same headings as the 
> TblPrices but ths is not what I need as I need to reference current price A 
> (or B or C) in another part of the database.
> -- 
> Very Grateful for all and any help.
> Steve