How do I Transfer Data from one table to another

  • Thread starter Thread starter Steve
  • Start date Start date
S

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 TblCureentPrice 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.
 
Instead of copying the data, you can use 2 queries.
The first query will find the most recent date in tblPrices.
Use just the date column.
Change the query to a totals query (right click on the field name row and
choose totals).
Where the query says GroupBy, click the drop down arrow and choose Max.
Switch to datasheet view and check that it is returning only one row with
the most recent date.

The next query will be based on tblPrices.
Select the date and the price fields.
Then add the first query to the query and join tlbPrices to it on the Date
field.

You now have a query showing the latest prices.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Jeanette

Thanks for this, I like your way of working and I am really grateful for the
response however this does not solve my problem. In the first table
TblPrices, I have prices in relation to the feld of A or B or C, but in the
TblCurrentPrice I would like the current price price in relation to the field
Product. I have already managed to get the current price in the same format
as the TblPrice layout using a query which returns the top 1 when sorted
descending, wth respect to date. This does the same as you reply but only
using 1 query. I am looking to poulate the TblCurrentPrice table in the
Current Price column because I reference this elsewhere in the database. If
it cannot be done I will have to rewrite a large part of the database, but at
least I will know.
 
Try this --
SELECT "A" AS [Product], [A] AS [Current Price]
FROM TblPrices
WHERE [Date] = (SELECT Max([XX].[Date]) FROM TblPrices AS [XX])
UNION ALL SELECT "B" AS [Product], AS [Current Price]
FROM TblPrices
WHERE [Date] = (SELECT Max([XX].[Date]) FROM TblPrices AS [XX])
UNION ALL SELECT "C" AS [Product], [C] AS [Current Price]
FROM TblPrices
WHERE [Date] = (SELECT Max([XX].[Date]) FROM TblPrices AS [XX]);
 
Karl, thanks so much for posting this. It nearly does it but when it amends
the current price table it lists every price for A, then every price for B
and C. I'm not great with Sql so I just copied your query straight into a new
query.

This is as near as I have come to getting what I need, I was wondering if
the SELECT Max([XX], Date might be the issue, do need to put in something
else here. When I run the query it does ask me for two dates in a parameter
box.

Thanks once again I really appreciate it, and if anyone can figure out how
to select only the most recent date, that's really make my day :)

Thanks
--
Very Grateful for all and any help.
Steve


KARL DEWEY said:
Try this --
SELECT "A" AS [Product], [A] AS [Current Price]
FROM TblPrices
WHERE [Date] = (SELECT Max([XX].[Date]) FROM TblPrices AS [XX])
UNION ALL SELECT "B" AS [Product], AS [Current Price]
FROM TblPrices
WHERE [Date] = (SELECT Max([XX].[Date]) FROM TblPrices AS [XX])
UNION ALL SELECT "C" AS [Product], [C] AS [Current Price]
FROM TblPrices
WHERE [Date] = (SELECT Max([XX].[Date]) FROM TblPrices AS [XX]);


--
Build a little, test a little.


Steve said:
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 TblCureentPrice 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.
Click to expand...
Click to expand...
 
Steve said:
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 TblCureentPrice 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.
Click to expand...
 
Back
Top