?
Solved

How to do a Dlookup in Access?

Posted on 2012-09-17
6
Medium Priority
?
246 Views
Last Modified: 2012-11-30
Hi Guys, I have a query called New Kondor and a column called "New Kondor ID" and I need to bring in the Old Kondor ID via a Lookup table in Access called Iam Mapping Table. Can anyone help me bring it in?
db1.mdb
0
Comment
Question by:JCutcliffe
  • 4
  • 2
6 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38406027
Is the New Kondor ID included in the table [Iam Mapping] ???

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38406056
If the New Kondor ID is in the lookup table then ...

In your query add ...

Old_Kondor_ID: DLookup("[Old_Kondor]", "Iam Mapping", "[New Kondor ID] = '" & [New Kondor ID] & "'")

Or, you could set a relationship between the two tables based on the New Kondor ID field then include the Old Kondor ID field in your query results.

ET
0
 

Author Comment

by:JCutcliffe
ID: 38407415
Hi ET, the mapping table is called "Iam Mapping" so should that be not be in square brackets?

I am trying to bring back the field called "Old Kondor Id" in the "New Kondor Id" field within the Query "New Kondor". The mapping table has both "Old Kondor Id" and "New Kondor ID" fields.

Justin
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
Eric Sherman earned 2000 total points
ID: 38407477
<<<<<Hi ET, the mapping table is called "Iam Mapping" so should that be not be in square brackets?>>>>>

"Iam Mapping" =  the Domain in the DLookup paramaters ... enclosed in double quotes will work.

<<<<<I am trying to bring back the field called "Old Kondor Id" in the "New Kondor Id" field within the Query "New Kondor". The mapping table has both "Old Kondor Id" and "New Kondor ID" fields.>>>>>

Sorry, the statement above is a bit confusing  ....  <<<<<I am trying to bring back the field called "Old Kondor Id" in the "New Kondor Id" field within the Query "New Kondor".

I tried to open your sample but there is nothing in it.  Can you upload a sample of your db.  Should be pretty easy.  I need to know which table has the "Old Knodor Id" along with the "New Kondor Id".

ET
0
 

Author Comment

by:JCutcliffe
ID: 38407544
Hi, the table which has both fields in is called "Iam mapping". My query "New Kondor" only has "New Kondor Id" and I am trying to import into it the "Old Kondor Id" field.


Justin
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38407663
Ok, sounds like you want to update the [Old Kondor Id] field with the values in the [New Kondor Id] field for each record in table [Iam mapping].

1.) Make a copy of your Iam mapping table first or backup your current db.
2.) Run the query below:

UPDATE [Iam mapping] SET [Old Kondor Id] = [New Kondor Id];


ET
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question