How do I use Expression Builder in Access to create a LOOKUP function?

Question

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.

Answer 1

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.

Answer 2

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