Solved Converting a Concatenated VLookup from Excel to Access (1 Viewer)

[RobertC004]

Hello!

I'm new to this forum... but have found a lot of answers to questions in the past. I'm pretty well versed in Excel, but Access stumps me a bit, at times.

I have a .txt file that gets refreshed each week. In the front end DB, we run queries, which then pushes the synthesized data to a BackEnd DB.

There is a lookup table in Access that looks a lot like the one below:

Type Code	Type Text	Reason Code	Reason Text
XG	Change of Position	X5	Promotion-Regular
XQ	Transfer	X5	Transfer Back
XG	Change of Position	X6	Promotion-Growth
XQ	Transfer	X6	Transfer - Line of Business

In the report that spits out to .txt file, I would get the following data:

Type Code	Type Text	Reason Code	Reason Text
XG	Change of Position	X5	<blank>
XQ	Transfer	X5	<blank>
XG	Change of Position	X6	<blank>
XQ	Transfer	X6	<blank>

In the formatted report, at the end, I need to have all four columns populated.

How could I, in query form, get Access to "fill in" the "Reason Text" column?

It's not a straight up join, as the Reason Text is dependent on the combination of Type Code & Reason Code.

I was thinking about doing an Update Query in two steps... or possibly splitting the Lookup Table into two... but I'm thinking there might be an easier solution.

In Excel, I would likely do a VLookup on the Concatenation of the Two Codes... but i'm not even sure that's a solution in Access.

Any suggestions?

Thank you!

 

[arnelgp]

say A=lookup table
and B the raw table

select B.*, A.[Reason Text]
    from B Left Join A
          On B.[type code]=A.[type code]
          And B.[type text]=A.[type text]
          And B.[reason code]=A.[reason code];

 

[namliam]

Yeap a simple join, except your using multiple columns to join on which isnt a problem if that is te requirement and a common one at that.
Many joines join the two tables on multiple columns

As for the suggestion @angelgp gave, joining on a "text" field is generaly a bad idea, as you descibe the two code fields will suffice.

select B.*, A.[Reason Text]
from B 
Left Join A
          On B.[type code]=A.[type code]
          And B.[reason code]=A.[reason code];

If you are using the query designer in access simply add you two tables, then drag both code fields on top of the other 1 by 1.

Good luck

 

[RobertC004]

Awesome... thank you, both. I was trying to do other joins... and the direct join on both fields was exactly what i needed. Thanks!