Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I recently moved from Excel 2007 to Access 2007 to manage what never should have been spreadsheets. Unfortunately, from what I can tell, the LOOKUP function in Excel does not exactly work the same as a Lookup column in Access.

My difficulty with Access lies in translating the functionality from an Excel a LOOKUP that may have looked like this:

=LOOKUP(A47, Winning!A1:A112, Winning!B1:B112)

I am having trouble building an expression that captures this functionality. In searching for an explanation on how to populate one column using data entered from a previous one, I have found various methods involving filters and VBA, but no explanation of how they work and I am reluctant to simply drop in code if I don't have much of an explanation of how it works.

  • How do I populate one column using the input from a previous one?
    • Is there a way to do so using relationships, or is an expression a better method?
    • I am expecting to be entering either an expression into a selection filter that selects based on the input column, or something akin to DATE() where a default value is dropped based on COLUMNNAME([FILTER]); are there integrity issues with this approach?
  • Would DLookup be a more effective tool here? Something like;
    • DLookup([CCD].[Name], [CCDBackend], [CCD].[ClientID]=[CCDBackend].[ClientID])
  • Is there a resource available for the Excel-addled brain trying to reforge itself in some Access accessible way?

I'm trying not clog up the question queue with inane iterations of VBA gobbledygook. Please bear in mind that for this question explanations of answers and methods will be more helpful than a code snippet.

share|improve this question
In Access a query is the built-in look up tool. Have you tried to use a query? An append query results are added to an existing table. – CharlieRB Aug 9 '12 at 17:05
@CharlieRB I have successfully done/used a few queries, made lookup columns; am I missing the boat on something utterly fundamental where basically you enter something in Column A, then Column B takes that value, references an analog Column A in a separate table, and returns the analog Column B value? – mfg Aug 9 '12 at 17:12
Here is an article which might be helpful - Teach Access to Excel users by comparing features. It's from 2002, but it has some comparisons which will help you understand Access better. – CharlieRB Aug 9 '12 at 17:43
up vote 2 down vote accepted

Access doesn't look at the data the same way Excel does. In an Access table, data is stored within records (cells) of a field (column). Records can not be referenced in other "cells" of the table like Excel allows.

To copy data from one record to another only one time, you can open the table in datasheet view and copy and paste a column. If you think you will need to do this more than once or want to filter it with a criteria, an append query would probably help you the best.

Without getting into the depths of creating a query, here is an article, Using Expression Builder In Access 2010, which shows how to use Expression Builder where they add a field to a table then populate data to it using data from another field.

This should give you a basic idea how this works enough to complete your task.

share|improve this answer
Oops, sorry about the version. I believe it will be the same basic task. – CharlieRB Aug 9 '12 at 18:28

The excel funtion =LOOKUP(A47, Winning!A1:A112, Winning!B1:B112) can be changed to a dlookup (but if it's done frequently, you may want to use recordsets or queries)

The translation would be:

Result=dlookup("[B Column]","Table with A & B Data","[A column]=A47")

of course, I've taken some liberties in the names used, as I don't know exactly how your data is stored

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .