Getting info from two tables with disimilar headings

[Steve]

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.

 

[Dorian]

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".