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
|
|||
|
|||
Still confused about queries to similate vlookup
Normally I would just dump this into excel, but the file that I am dealing
with has over 1.5 million rows so excel can't stomach it. Here is what I have done. I have one table (MAIN) that is my main table. It contains all the raw data. I need to convert a city code to a city name. I have another table (CITY) with the conversions from city code to city name. Both tables contain all the city codes. All I want to do is put the city names into my MAIN table next to their corresponding city code (there are about 4000 different cities). I have related the tables relating the city code and tried making a query but I am lost. What do I need to do. |
#2
|
|||
|
|||
Still confused about queries to similate vlookup
On Thu, 4 Mar 2010 09:11:02 -0800, Dan wrote:
Normally I would just dump this into excel, but the file that I am dealing with has over 1.5 million rows so excel can't stomach it. Here is what I have done. I have one table (MAIN) that is my main table. It contains all the raw data. I need to convert a city code to a city name. I have another table (CITY) with the conversions from city code to city name. Both tables contain all the city codes. All I want to do is put the city names into my MAIN table next to their corresponding city code (there are about 4000 different cities). I have related the tables relating the city code and tried making a query but I am lost. What do I need to do. Queries are your friends. They make it *UNNECESSARY* to "put the city names into your MAIN table" at all. Instead, create a Query. Add the MAIN table to the query, then add the CITY table. Join the two tables on the city code. Select whatever fields you want to see from the MAIN table, and the city name from the CITY table (you don't need to select the city code at all, unless you have some reason to see it). Now you can open this query - *not your table* - to see the data in conjunction. Better, you can base a Form (for onscreen viewing) or Report (for printing), *on the query*, not on the table. Tables have only one function - data storage. If you're opening the table to edit or view your data, you're missing that fact! Tables aren't designed for that purpose. Take a look at the resorces here, particularly the tutorials - Crystal's video might be worth a look. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Still confused about queries to similate vlookup
Thank you John. It worked! I've spent way to long trying to make access act
like excel. Your poste has helped me understand why I shouldn't. "John W. Vinson" wrote: On Thu, 4 Mar 2010 09:11:02 -0800, Dan wrote: Normally I would just dump this into excel, but the file that I am dealing with has over 1.5 million rows so excel can't stomach it. Here is what I have done. I have one table (MAIN) that is my main table. It contains all the raw data. I need to convert a city code to a city name. I have another table (CITY) with the conversions from city code to city name. Both tables contain all the city codes. All I want to do is put the city names into my MAIN table next to their corresponding city code (there are about 4000 different cities). I have related the tables relating the city code and tried making a query but I am lost. What do I need to do. Queries are your friends. They make it *UNNECESSARY* to "put the city names into your MAIN table" at all. Instead, create a Query. Add the MAIN table to the query, then add the CITY table. Join the two tables on the city code. Select whatever fields you want to see from the MAIN table, and the city name from the CITY table (you don't need to select the city code at all, unless you have some reason to see it). Now you can open this query - *not your table* - to see the data in conjunction. Better, you can base a Form (for onscreen viewing) or Report (for printing), *on the query*, not on the table. Tables have only one function - data storage. If you're opening the table to edit or view your data, you're missing that fact! Tables aren't designed for that purpose. Take a look at the resorces here, particularly the tutorials - Crystal's video might be worth a look. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|