lookup table value

Question

OK, I am embarrassed I cannot get this easy question.

Even after looking at several other posts.

Just as a learning step, I am trying to lookup 1 field from 1 table to another

So I have a table lookup.  with Field1 and Field2

I have table1 with Field1 and Field2

Field1 in both tables are equal.  I am trying to just pull Field2 from lookup to table1

I put in a calculated field with the query

select a.Field2

from Lookup a inner join table1 b on a.Field1= b.Field1

And error happens.

Any help would be appreciated

Answer 1

So is there a way to an excel like vlookup in a table?

Take a look at the DLookup function in Help.

But mostly you won't use it, you'd join the tables in a query.  So if we take Scott's example of a Colors table, and assume we have a Vehicles which includes a foreign key ColorID column, then we might have a query along these lines:

SELECT Vehicles.Model, Colors.Color

FROm Vehicles INNER JOIN Colors

ON Vehicles.ColorID = Colors.ColorID;

This would show the model and colour of each vehicle

In a form for entering data you'd base the form on the Vehicles table and include a combo box set up as follows:

ControlSource:  ColorID

RowSource:     SELECT ColorID, Color FROM Colors ORDER BY Color;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the dimension is zero to hide the first column.

To enable you to enter a new colour not currently represented in the Colors table you'd put the following code in the combo box's NotInList event procedure, which would insert a new row into Colors, subject to user confirmation, when you type the new colour into the combo box:

    Dim ctrl As Control

    Dim strSQL As String, strMessage As String

    Set ctrl = Me.ActiveControl

    strMessage = "Add " & NewData & " to list?"

    strSQL = "INSERT INTO Colors(Color) VALUES(""" & _

            NewData & """)"

    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

        CurrentDB.Execute strSQL, dbFailOnError

        Response = acDataErrAdded

    Else

        Response = acDataErrContinue

        ctrl.Undo

    End If

Answer 2

Access is not "Excel on Steroids". It's a different program, with different base assumptions!

One key point is that Tables are for data storage - and that is all that they are for! Table datasheets have very limited functionality as user interfaces, and should be kept "under the hood"; basically all user interaction with data should be done on Forms.

It's perfectly straightforward to put Combo Boxes on a Form, which can store one value (a foreign key numeric ID typically) in the table, while displaying a different value (a human-meaningful text) on the screen. It is NOT necessary to use a table lookup in order to accomplish this.

Get away from the idea that "a table is just a spreadsheet". It's not.

Answer 3

Thanks all,

So is there a way to an excel like vlookup in a table?

Answer 4

In addition to what John has said, that NOT how lookups are used. 

Lookups are used to standardize data entry by requiring the user select from a list of items rather than type the data in. A lookup table will work in one of two ways. Either the lookup table will be a single field with a list of items that the user chooses from. In that case, the value selected is copied into the table. The other, much more common, method is for the lookup table to have at least two fields, one a numerical code (usually an autonumber in Access) and a description field which is what the user sees and selects from. So you might have a table called Colors. That table would have fields for ColorID and Color and look like this:

ColorID  Color

1            Red

2            Green

3            Yellow

etc.

Lookups use list controls, primarily comboboxes, to display the description field, but store the ID field (when one is used. So the user sees a list of Red, Green, Yellow etc. but 1, 2 or 3 respectively is stored in the table. When doing reports, the report is based on a query that Joins the main table to the lookup tables to pull the description field into the query.

Answer 5

Well...

Don't.

Most serious developers really dislike the table-lookup feature. See http://www.mvps.org/access/lookupfields.htm for a critique.