Query with lookup from second table

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
537
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a query which pulls records from Table A. I want to include a descriptive field in my query that only resides in Table B. How can I go about doing so?

Table B has two fields, the ID I want to match on and the description I want to display. But I need to parse and concatenate two fields from Table A to get the ID to match on, like so:
Mid([Field1],2,3) & [Field2]

I can't figure out what to do next however.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
A few ways to go about this:

1. You could use the DLOOKUP function to look up the value you want. I think you should be able to concatenate fields to create your lookup field in that function. See MS Access: DLookup Function for help on the DLOOKUP function.

2. You could create a query where you concatenate field1 and field2 to get your matching lookup field. Then create a new query joining this query to your other table, and then you should be able to get what you want.
 
Upvote 0
I came across the DLookup after searching online but kept getting different error messages. I'm sure I don't have the correct syntax or the order is out of place.

Expr1: DLookUp("[CCDescr]","[CCLookup]", Mid([Field1],2,3) & [Field2]=Query![CCQuery]![FullCC]")

CCDescr is the field I want to display
CCLookup is the table where it resides
Mid([Field1],2,3) & [Field2] is my field concatenation
CCQuery is the name of the query
FullCC is the field I need to match against the concatenated field
 
Upvote 0
I think you may have a problem with the third (last) argument in the function. This first part of it should be a field name, not a field value (take a look at the link I provided in the previous post), and it usually needs to be surrounded in quotes. Without seeing what your data looks like, it it difficult to ascertain if your logic is solid.

Did you try the other alternative I presented?
 
Upvote 0
And there is option 3.

Select Field1, Field2, (Select Description From TableB Where ID = Mid(TableA.Field1,2,3) & TableA.Field2) As Description from TableA
 
Upvote 0
I hate to revive this thread but I'm still struggling with this issue. I was able to get the lookup working by using the "select field" option but it throws an error when I try to add the field to my report ("Multi-level GROUP BY clause is not allowed in a subquery."). I've attempted to go back and get the DLookup working with no success. It either returns "#Error" when I view the records or tells me that it can't find the name 'Division" I entered in the expression depending on the syntax.
Code:
DLookUp("CCDescr","CCLookup","CCID = Mid([Division],2,3) & [Department]")
 
Upvote 0
Finally got the DLookup to work. It was a matter of enclosing the string in single quotes. Now I can use the field in my report and it does not throw the "Multi-level GROUP BY" error I was receiving before.
Code:
DLookUp("CCDescr","CCLookup","CCID = '" & Mid([Division],2,3) & [Department] & "'")
Thank you Joe and Kreszch for all of your help!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top