Auto-Populate a field with data in different table (1 Viewer)

ayanam

New member
Local time
Today, 19:36
Joined
Feb 1, 2016
Messages
2
Hi, I have a table that is imported and the choices in several fields need to be converted. For instance

In my table, if the field name "SF Denial Reason" has a value of 001 Debt to Assets
The field "DB Denial Reason", in the same table, should populate with A20 .

I have a field mapping table that list all possible choices for "SF Denial Reason" and the corresponding "DB Denial Reason" values.

I need this auto-population to happen for several fields that all have different field mapping tables. If this was Excel, I would use a vlookup to accomplish this.

Enter VLOOKUP formula in Field 2 Table A:
=VLOOKUP(Field 1 Table A, Table B, Table B Field 2)

Translation: Look in Field 1 Table B for value in Field 1 Table A
If found, update Field 2 Table A with corresponding value in Field 2 Table B
How can I get this to happen? Basically I have an imported file that has user-friendly values that I need to convert into database values so it can be exported to CSV and uploaded into a data warehouse
 

shiznaw

Registered User.
Local time
Today, 16:36
Joined
Feb 3, 2016
Messages
18
A Simple Update Query might do. The SQL View in the Ribbon
SQL View - UPDATE Table1 SET Table1.[DB Denial Reason] = "A20"
WHERE (((Table1.[SF Denial Reason])="001 Debt to Assets"));
 

Attachments

  • Design View.JPG
    Design View.JPG
    51 KB · Views: 97

ayanam

New member
Local time
Today, 19:36
Joined
Feb 1, 2016
Messages
2
Thanks to you both.
Response 1: I haven't worked in Access in a few...or more years. I was totally overthinking this. I tried the simple query but forgot to set up the relationships. Now that I have done that, it works perfectly.

Response 2: That would work if that was the only conversion. the denial reasons table has 52 reasons that match up to 9 reason codes. that would be way too many to do by manually entering the criteria for each of the reasons. I did attempt an update with dlookup in the update to field but as I added fields it seem to get messy. The 1st response worked perfectly.
 

Users who are viewing this thread

Top Bottom