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
|
|||
|
|||
Getting info from two tables with disimilar headings
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 |
#2
|
|||
|
|||
Getting info from two tables with disimilar headings
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 |
Thread Tools | |
Display Modes | |
|
|