Vlookup in Access

Hi

I have two tables with data in Access called "118" and "Mapping":

In "118" table I'll have my monthly data, which will show amounts (in my case 1 row equals one item/amount), by ZIP code. In "Mapping" table I have two columns which is all ZIP codes and their allocated Clusters.

What I want is to have a field in "118" showing Clusters. Hence, when I dump in data each month/quarter the field just autopopulated. Basically like a Vlookup function in XLS.

I'm a beginner in Access so can't do programming and all this.

Hope you understand and can help.

Please provide the field names for each of the tables and some examples of what the data looks like and what you would like the expected results to be. I am sure that what you want will be a query with either a parameter that is filled in from a form or ad hoc. Give us some more information to allow us to provide you with a suitable SQL statement for your query.

Hi,
You can use powerpivots in excel.
Here are some steps :-
1. Pull into DataModel both tables in excel using powerquery or simply from powerpivot window. (You can google for this.)
2. Establish a relation between zip column of both 118 and Mapping table.
3. Based on the relationship in both the tables, you can then make pivot tables having slicers for zip columns or clusters.

Its is a powerful way to do it.

Hope it helps!!

Powerpivot can handle millions of row easily. That's why I said its powerful.

Edit :-
Moreover, you can use sql queries directly in powerpivot, using powerquery or from the powerpivot window, to query your sql servers. This way you can avoid Access for your reporting.

Hello,
As I am using Excel 2013 and you, I guess, are using 2010. I am not sure if file would show the stuff or not.
But I have achieved what you want.

Here are the steps :-
1. Open the Powerpivot window and click the Diagram View Button.
2. Now you need to create a relationship between Zip column of Mapper and 118 tables.
3. For that, you need to Left click , hold and drag the Zip Column in the Mapper Table on the Zip column of the 118 Table. You can google on how to create relations on powerpivot. FYI, that would be a One (Mapper) to many (118) relationship between the two tables.
4. After you established the relationship, you need to pull the Clusters on the 118 table. For that, use the Related function.

Please Login or Register  to view this content.

Add a column with above formula.
These were the steps. After that, you would be able to pull down a pivot having all the columns including new Cluster column.

Hope this suffices.!!