Prev: Joining Multiple Tables
Next: howto copy only newer OrderID data(and the other fields) from one table to another table
From: Steve on 1 Mar 2010 11:34
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
From: Dorian on 1 Mar 2010 11:47
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
 | 
Pages: 1
Prev: Joining Multiple Tables
Next: howto copy only newer OrderID data(and the other fields) from one table to another table