If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I Transfer Data from one table to another
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. -- Very Grateful for all and any help. Steve |
#2
|
|||
|
|||
How do I Transfer Data from one table to another
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 "Steve" wrote in message ... 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. -- Very Grateful for all and any help. Steve |
#3
|
|||
|
|||
How do I Transfer Data from one table to another
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. -- Very Grateful for all and any help. Steve "Jeanette Cunningham" wrote: 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 "Steve" wrote in message ... 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. -- Very Grateful for all and any help. Steve . |
#4
|
|||
|
|||
How do I Transfer Data from one table to another
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], [b] 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" 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 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. -- Very Grateful for all and any help. Steve |
#5
|
|||
|
|||
How do I Transfer Data from one table to another
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" wrote: 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], [b] 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" 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 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. -- Very Grateful for all and any help. Steve |
#6
|
|||
|
|||
How do I Transfer Data from one table to another
"Steve" wrote in message ... 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. -- Very Grateful for all and any help. Steve |
#7
|
|||
|
|||
How do I Transfer Data from one table to another
|
Thread Tools | |
Display Modes | |
|
|