I have a table full of code IDs and their descriptions in access. And in another table is a field that has code IDs that correlate to the IDs in the Codes table. I am trying to design a macro that when executed will replace the code ID in the second table with the correct description but I am unsure a way to do this. I was thinking of using a SQL Insert query to do so but am unsure of what the statement would look like.
JOIN statement:
SELECT ShouldImportMetricsIDsTable.FORMULARYID, ReasonCodes.Description
FROM ShouldImportMetricsIDsTable,ReasonCodes
INNER JOIN ReasonCodes
ON ShouldImportMetricsIDsTable.ReasonCode=ReasonCodes.CodeID
Mention ReasonCodes only once in your query's FROM clause.
Change this ...
FROM ShouldImportMetricsIDsTable,ReasonCodes
INNER JOIN ReasonCodes
To this ...
FROM ShouldImportMetricsIDsTable
INNER JOIN ReasonCodes
As general advice, I suggest you begin your queries in the Access query designer. At least choose the data sources (tables or saved queries) and set up joins there.
With your original example, the designer would have applied an alias, ReasonCodes_1, for one of those duplicate ReasonCodes names. And that could be an early warning that the data sources aren't correct.